PKやUnique Indexがあるテーブルに対して既に存在するデータをINSERTしようとすると、
Duplicate Key Errorが起きるが、MYSQLでは幾つかDuplicate Key Errorの場合にどのように処理するか対応する方法がある。

dup

—————————————————————————————————-
IGNORE
重複データのINSERTを無視します。
—————————————————————————————————-


mysql> select * from T_WITH_INDX;
+------+--------+--------+
| col1 | col2 | col3 |
+------+--------+--------+
| 1 | col2_1 | col3_1 |
| 2 | col2_2 | col3_2 |
+------+--------+--------+
2 rows in set (0.00 sec)

mysql> insert into T_WITH_INDX values(1,'col2_1','col3_1');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert ignore into T_WITH_INDX values(1,'col2_1','col3_1');
Query OK, 0 rows affected (0.00 sec)

mysql> select * from T_WITH_INDX;
+------+--------+--------+
| col1 | col2 | col3 |
+------+--------+--------+
| 1 | col2_1 | col3_1 |
| 2 | col2_2 | col3_2 |
+------+--------+--------+
2 rows in set (0.01 sec)

mysql>

ignore

—————————————————————————————————-
REPLACE
既存のデータをDELETEして新しいデータがINSERTされます。
—————————————————————————————————-


mysql> select * from T_WITH_INDX;
+------+--------+--------+
| col1 | col2 | col3 |
+------+--------+--------+
| 1 | col2_1 | col3_1 |
| 2 | col2_2 | col3_2 |
+------+--------+--------+
2 rows in set (0.00 sec)

mysql> replace into T_WITH_INDX values(1,'col2_1','col3_1(2)');
Query OK, 2 rows affected (0.00 sec)

mysql> select * from T_WITH_INDX;
+------+--------+-----------+
| col1 | col2 | col3 |
+------+--------+-----------+
| 1 | col2_1 | col3_1(2) |
| 2 | col2_2 | col3_2 |
+------+--------+-----------+
2 rows in set (0.00 sec)

mysql>

replace


mysql> select * from T_WITH_INDX;
+------+--------+-----------+
| col1 | col2 | col3 |
+------+--------+-----------+
| 1 | col2_1 | col3_1(2) |
| 3 | col2_2 | col3_2(2) |
+------+--------+-----------+
2 rows in set (0.00 sec)

mysql> insert into T_WITH_INDX values(2,'col2_2','col3_2(2)');
ERROR 1062 (23000): Duplicate entry 'col2_2' for key 'col2'
mysql> replace into T_WITH_INDX values(2,'col2_2','col3_2(2)');
Query OK, 2 rows affected (0.00 sec)

mysql> select * from T_WITH_INDX;
+------+--------+-----------+
| col1 | col2 | col3 |
+------+--------+-----------+
| 1 | col2_1 | col3_1(2) |
| 2 | col2_2 | col3_2(2) |
+------+--------+-----------+
2 rows in set (0.00 sec)

replace2


mysql> select * from T_WITH_INDX;
+------+--------+-----------+
| col1 | col2 | col3 |
+------+--------+-----------+
| 1 | col2_1 | col3_1(2) |
| 2 | col2_2 | col3_2(2) |
+------+--------+-----------+
2 rows in set (0.00 sec)

mysql> replace into T_WITH_INDX values(1,'col2_2','col3_2(3)');
Query OK, 3 rows affected (0.00 sec)

mysql> select * from T_WITH_INDX;
+------+--------+-----------+
| col1 | col2 | col3 |
+------+--------+-----------+
| 1 | col2_2 | col3_2(3) |
+------+--------+-----------+
1 row in set (0.00 sec)

mysql>

replace3

—————————————————————————————————-
ON DUPLICATE KEY UPDATE
既存のデータを変更(UPDATE)します。
—————————————————————————————————-


mysql> select * from T_WITH_INDX;
+------+--------+-----------+
| col1 | col2 | col3 |
+------+--------+-----------+
| 1 | col2_1 | col3_1(2) |
| 2 | col2_2 | col3_2(2) |
+------+--------+-----------+
2 rows in set (0.01 sec)

mysql> insert into T_WITH_INDX values(2,'col2_2(2)','col3_2(2)');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> insert into T_WITH_INDX values(2,'col2_2(2)','col3_2(2)')
-> ON DUPLICATE KEY UPDATE col1 = col1+1;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from T_WITH_INDX;
+------+--------+-----------+
| col1 | col2 | col3 |
+------+--------+-----------+
| 1 | col2_1 | col3_1(2) |
| 3 | col2_2 | col3_2(2) |
+------+--------+-----------+
2 rows in set (0.00 sec)

mysql>

dup_key


mysql> select * from T_WITH_INDX;
+------+-----------+-----------+
| col1 | col2 | col3 |
+------+-----------+-----------+
| 2 | col2_2(3) | col3_2 |
| 1 | col2_2 | col3_2(3) |
| 3 | col2_3 | col3_3 |
+------+-----------+-----------+
3 rows in set (0.00 sec)

mysql> insert into T_WITH_INDX values(1,'col2_2(2)','col3_1')
-> ON DUPLICATE KEY UPDATE col2 = 'col1_1';
Query OK, 2 rows affected (0.00 sec)

mysql> select * from T_WITH_INDX;
+------+-----------+-----------+
| col1 | col2 | col3 |
+------+-----------+-----------+
| 2 | col2_2(3) | col3_2 |
| 1 | col1_1 | col3_2(3) |
| 3 | col2_3 | col3_3 |
+------+-----------+-----------+
3 rows in set (0.00 sec)

on_dup


マルチバイト以外では同じ値を返すが、マルチバイトだと異なった値が返ってくる。

CHAR_LENGTH(str)

文字列 str の長さ(文字数)を返す。 マルチバイト文字は 1 文字とみなされる。
したがって、5 個のマルチバイト文字で構成される文字列の場合、LENGTH() では 10 が返るが、CHAR_LENGTH() では 5 が返る。

LENGTH(str)

文字列 str の長さ(バイト)を返す。1個のマルチバイト文字は複数バイトになる。
したがって、5 個の 2 バイト文字で構成される文字列の場合、LENGTH() では 10 が返るが、CHAR_LENGTH() では 5 が返る。


mysql> select LENGTH('HOW-LONG?');
+---------------------+
| LENGTH('HOW-LONG?') |
+---------------------+
| 9 |
+---------------------+
1 row in set (0.00 sec)

mysql> select CHAR_LENGTH('HOW-LONG?');
+--------------------------+
| CHAR_LENGTH('HOW-LONG?') |
+--------------------------+
| 9 |
+--------------------------+
1 row in set (0.00 sec)

mysql>

lenght


mysql> select LENGTH('長さはどのくらいだろうか');
+------------------------------------+
| LENGTH('長さはどのくらいだろうか') |
+------------------------------------+
| 24 |
+------------------------------------+
1 row in set (0.00 sec)

mysql> select CHAR_LENGTH('長さはどのくらいだろうか');
+-----------------------------------------+
| CHAR_LENGTH('長さはどのくらいだろうか') |
+-----------------------------------------+
| 16 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql>

length_2

—————————————————————————————————————
追記:  BINARY比較
—————————————————————————————————————

mysql> SELECT MD5('english') = MD5('ENGLISH');
+---------------------------------+
| MD5('english') = MD5('ENGLISH') |
+---------------------------------+
| 0 |
+---------------------------------+
1 row in set (0.00 sec)

md5

mysql> SELECT IF('ABC' = 'abc','TRUE','FALSE');
+----------------------------------+
| IF('ABC' = 'abc','TRUE','FALSE') |
+----------------------------------+
| TRUE |
+----------------------------------+
1 row in set (0.01 sec)

mysql> SELECT IF('ABC' = BINARY 'abc','TRUE','FALSE');
+-----------------------------------------+
| IF('ABC' = BINARY 'abc','TRUE','FALSE') |
+-----------------------------------------+
| FALSE |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT IF(BINARY 'ABC' = BINARY 'abc','TRUE','FALSE');
+------------------------------------------------+
| IF(BINARY 'ABC' = BINARY 'abc','TRUE','FALSE') |
+------------------------------------------------+
| FALSE |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql>

binary_check


大文字/小文字を混ぜ合わせてCHARのテーブルとBINARYのテーブルに
データをINSERTしてCOUNTなどで結果を比較。

TABLE CREATE WITH CHAR


mysql> desc ENGLISH;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from ENGLISH;
+---------+
| name |
+---------+
| English |
| english |
| ENGLISH |
| eNgLiSh |
+---------+
4 rows in set (0.00 sec)

mysql> select distinct name from ENGLISH;
+---------+
| name |
+---------+
| English |
+---------+
1 row in set (0.00 sec)

mysql> select name,count(*) from ENGLISH group by name;
+---------+----------+
| name | count(*) |
+---------+----------+
| English | 4 |
+---------+----------+
1 row in set (0.00 sec)

char_table

TABLE CREATE WITH BINARY


mysql> desc ENGLISH_BIN;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name | binary(10) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from ENGLISH_BIN;
+------------+
| name |
+------------+
| English |
| english |
| ENGLISH |
| eNgLiSh |
+------------+
4 rows in set (0.00 sec)

mysql> select distinct name from ENGLISH_BIN;
+------------+
| name |
+------------+
| English |
| english |
| ENGLISH |
| eNgLiSh |
+------------+
4 rows in set (0.00 sec)

mysql> select name,count(*) from ENGLISH_BIN group by name;
+------------+----------+
| name | count(*) |
+------------+----------+
| ENGLISH | 1 |
| English | 1 |
| eNgLiSh | 1 |
| english | 1 |
+------------+----------+
4 rows in set (0.00 sec)

binary_table


IGNORE_SPACEを有効にしているとき、パーサはファンクション名と後続の括弧間に余白が存在
してはいけないという要求を緩和します。このことで、ファンクション呼び出しの記述がより自由に行える
ようになります。例えば、次のどちらのファンクション呼び出しも有効です。

SELECT COUNT(*) FROM mytable;
SELECT COUNT (*) FROM mytable;


mysql> SELECT NOW ();
ERROR 1305 (42000): FUNCTION NOW does not exist
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2009-07-25 00:03:31 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW ();
ERROR 1305 (42000): FUNCTION NOW does not exist
mysql> SET sql_mode = 'IGNORE_SPACE';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NOW ();
+---------------------+
| NOW () |
+---------------------+
| 2009-07-25 00:04:13 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2009-07-25 00:04:19 |
+---------------------+
1 row in set (0.00 sec)

mysql> select @@sql_mode;
+--------------+
| @@sql_mode |
+--------------+
| IGNORE_SPACE |
+--------------+
1 row in set (0.00 sec)

mysql>

sql_mode_space

参考サイト
—————————————————————-

8.2.4. 構文解析と解像度のファンクション名

4.2.6. SQL モード


INARYオペレータは2進性の文字列に続く文字列を送信します。キャラクタ毎よりも、
バイト毎の比較を強制的に行う簡単な方法です。BINARYは後続のスペースにも重要な意味を持たせます。

BINARY strはCAST(str AS BINARY)の略でもあります。


mysql> SELECT 'Hello world!' = 'Hello world!';
+----------------------------------+
| 'Hello world!' = 'Hello world!' |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 'Hello world!' = BINARY 'Hello world!';
+----------------------------------------+
| 'Hello world!' = BINARY 'Hello world!' |
+----------------------------------------+
| 1 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 'a' = 'a ';
+------------+
| 'a' = 'a ' |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT 'a' = BINARY 'a ';
+-------------------+
| 'a' = BINARY 'a ' |
+-------------------+
| 0 |
+-------------------+
1 row in set (0.00 sec)

mysql>

binary


mysql> SELECT @var := BINARY 'Lennart';
+--------------------------+
| @var := BINARY 'Lennart' |
+--------------------------+
| Lennart |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT @var;
+---------+
| @var |
+---------+
| Lennart |
+---------+
1 row in set (0.00 sec)

mysql> SELECT UPPER(@var);
+-------------+
| UPPER(@var) |
+-------------+
| Lennart |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT UPPER(CONVERT(@var USING utf8));
+---------------------------------+
| UPPER(CONVERT(@var USING utf8)) |
+---------------------------------+
| LENNART |
+---------------------------------+
1 row in set (0.00 sec)

mysql>

binary2

参考サイト
———————————————–
9.5.3. BINARY オペレータ


IF(expr1,expr2,expr3)

expr1 が TRUE である場合は ( expr1 <> 0 および expr1 <> NULL ) 、
IF() は expr2 を戻します。それ以外では、expr3 を戻します。IF() は、
使用されている文脈によって、数値値もしくはストリング値を戻します。


mysql> SELECT IF(1<2,'yes','no'); +--------------------+ | IF(1<2,'yes','no') | +--------------------+ | yes | +--------------------+ 1 row in set (0.00 sec) mysql> SELECT IF(3<2,'yes','no'); +--------------------+ | IF(3<2,'yes','no') | +--------------------+ | no | +--------------------+ 1 row in set (0.00 sec) mysql>

if


mysql> SELECT
-> IF(comment IS NOT NULL,
-> CONCAT(comment,' IS VALID'),
-> 'IS NULL DATA')
-> AS 'Happy or Sad'
-> FROM test.a10;
+---------------+
| Happy or Sad |
+---------------+
| IS NULL DATA |
| IS NULL DATA |
| IS NULL DATA |
| IS NULL DATA |
| data IS VALID |
| test IS VALID |
+---------------+
6 rows in set (0.00 sec)

mysql>

if_not

11.2. 制御フロー関数


UNION は、結果を複数 SELECT ステートメントから単一結果セットに
結合させる為に利用されます。
最初の SELECT ステートメントからのカラム名は、返された結果のカラム名
として利用されます。各 SELECT ステートメントの対応する位置にリスト
されている選択されたカラムは、同じデータ タイプを持つ必要があります。
(例えば、最初のステートメントに選択された最初のカラムは、
別のステートメントに選択された最初のカラムと同じタイプを持つ必要があります。)

————————————————
幾つかのテーブルに散らばった情報を1つの
Queryにて全て表示する為には、UNION ALLにて
テーブルを連結し情報を選択し抽出。
————————————————
SELECT
comment
FROM test.A10
UNION ALL
SELECT comment FROM test.a10
ORDER BY comment;

————————————————
幾つかのテーブルに散らばった情報を1つの
Queryにて全て表示する為には、UNION ALLにて対応。
しかし重複した情報は一つにまとめて表示する為には、
UNIONにて対応。(ALLは利用しない)
————————————————
SELECT
comment
FROM test.A10
UNION
SELECT comment FROM test.a10
ORDER BY comment;

union

12.2.7.2. UNION 構文


GROUP BY 句に WITH ROLLUP 修飾子を加えると、
クエリがすべてのデータにわたる総合計の値を示す行を生成します :

ROLLUP を使用する場合、ORDER BY 句を同時に使用して結果をソートすることはできません。
つまり、ROLLUP と ORDER BY は互いに排し合うということになります。
しかし、ソートの順番をいくらかコントロールすることは可能です。
MySQL の GROUP BY が結果をソートし、そして 明示的な ASC および DESC キーワードを
GROUP BY 内で名付けられたカラムと使用し、各カラムのソート順を指定することができます。
( しかし、ROLLUP によって加えられた高レベルな要約行は、ソート順に関わらず、
それらが計算された行の後に現れます。)

LIMIT はクライアントに戻される行の数を限定するのに使用できます。LIMIT は ROLLUP
の後に適用され、それによって ROLLUP によって追加された行に対しての制限が適用されます。


mysql> SELECT
-> ENGINE,
-> COUNT(*) AS Total
-> from information_schema.TABLES
-> where ENGINE IS NOT NULL
-> GROUP BY ENGINE;
+--------+-------+
| ENGINE | Total |
+--------+-------+
| CSV | 2 |
| InnoDB | 12 |
| MEMORY | 20 |
| MyISAM | 134 |
+--------+-------+
4 rows in set (0.01 sec)

mysql> SELECT
-> ENGINE,
-> COUNT(*) AS Total
-> from information_schema.TABLES
-> where ENGINE IS NOT NULL
-> GROUP BY ENGINE WITH ROLLUP;
+--------+-------+
| ENGINE | Total |
+--------+-------+
| CSV | 2 |
| InnoDB | 12 |
| MEMORY | 20 |
| MyISAM | 134 |
| NULL | 168 |
+--------+-------+
5 rows in set (0.04 sec)

roll_up

11.11.2. GROUP BY 修飾子


GROUP_CONCAT()

この関数は、グループからの連結された非 NULL 値を伴うストリング結果を戻します。
非 NULL 値がない場合は NULL を戻します。

GROUP_CONCAT() によって戻されるタイプは、group_concat_max_len が 512
より大きい場合意外は常に VARCHAR になります。512 を越える場合は BLOB になります。

GROUP_CONCAT([DISTINCT] expr [,expr …]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name …]]
[SEPARATOR str_val])


mysql> select
-> TABLE_SCHEMA,TABLE_NAME
-> from information_schema.TABLES
-> where TABLE_SCHEMA = 'test';
+--------------+----------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+----------------+
| test | A10 |
| test | T10 |
| test | a10 |
| test | innodb_monitor |
| test | innodbtable |
| test | t10 |
+--------------+----------------+
6 rows in set (0.00 sec)

mysql> select
-> TABLE_SCHEMA,
-> GROUP_CONCAT(TABLE_NAME SEPARATOR '/')
-> from information_schema.TABLES
-> where TABLE_SCHEMA = 'test';
+--------------+--------------------------------------------+
| TABLE_SCHEMA | GROUP_CONCAT(TABLE_NAME SEPARATOR '/') |
+--------------+--------------------------------------------+
| test | A10/T10/a10/innodb_monitor/innodbtable/t10 |
+--------------+--------------------------------------------+
1 row in set (0.00 sec)

group_concat1

    参考サイト

11.11.1. GROUP BY ( 集約 ) 関数


SELECT は1 つ以上のテーブルからレコードを選択して取り出すときに使用します。
各 select_expression は、取り出すカラムを表します。
SELECT は、どのテーブルも参照することなく、計算によって求められたレコードを取り出すときにも使用できます。


mysql> select
-> TABLE_NAME,
-> YEAR(CREATE_TIME) AS Year,
-> MONTH(CREATE_TIME) AS Month,
-> DAY(CREATE_TIME) AS DAY
-> from information_schema.TABLES
-> where TABLE_SCHEMA = 'test'
-> Order by Year DESC,Month DESC, DAY DESC;
+----------------+------+-------+------+
| TABLE_NAME | Year | Month | DAY |
+----------------+------+-------+------+
| A10 | 2009 | 7 | 23 |
| innodbtable | 2009 | 7 | 23 |
| T10 | 2009 | 7 | 23 |
| innodb_monitor | 2009 | 7 | 21 |
+----------------+------+-------+------+
4 rows in set (0.00 sec)

mysql>

table2

COLLATIONによってOrder byの結果も変わってきます。
(CI / CS / Binなど)


mysql> select
-> TABLE_NAME
-> from information_schema.TABLES
-> where TABLE_SCHEMA = 'test'
-> order by TABLE_NAME
-> COLLATE utf8_general_ci;

+----------------+
| TABLE_NAME |
+----------------+
| A10 |
| a10 |
| innodbtable |
| innodb_monitor |
| t10 |
| T10 |
+----------------+
6 rows in set (0.00 sec)

mysql> select
-> TABLE_NAME
-> from information_schema.TABLES
-> where TABLE_SCHEMA = 'test'
-> order by TABLE_NAME
-> COLLATE utf8_bin;
+----------------+
| TABLE_NAME |
+----------------+
| A10 |
| T10 |
| a10 |
| innodb_monitor |
| innodbtable |
| t10 |
+----------------+
6 rows in set (0.00 sec)

mysql>

cs

LIMIT 句


mysql> select
-> TABLE_NAME,
-> CREATE_TIME
-> from information_schema.TABLES
-> where TABLE_SCHEMA = 'test'
-> Order by CREATE_TIME
-> LIMIT 1;
+----------------+---------------------+
| TABLE_NAME | CREATE_TIME |
+----------------+---------------------+
| innodb_monitor | 2009-07-21 18:08:11 |
+----------------+---------------------+
1 row in set (0.00 sec)


choko

GROUP BY


mysql> select
-> ENGINE,
-> COUNT(*) AS Total
-> from information_schema.TABLES
-> where ENGINE IS NOT NULL
-> GROUP BY ENGINE;
+--------+-------+
| ENGINE | Total |
+--------+-------+
| CSV | 2 |
| InnoDB | 12 |
| MEMORY | 20 |
| MyISAM | 134 |
+--------+-------+
4 rows in set (0.01 sec)

mysql>

mysql> SELECT
-> ENGINE,
-> COUNT(*) AS Total
-> FROM information_schema.TABLES
-> GROUP BY ENGINE
-> HAVING ENGINE='MYISAM' OR ENGINE='INNODB'
-> ORDER BY Total DESC;
+--------+-------+
| ENGINE | Total |
+--------+-------+
| MyISAM | 134 |
| InnoDB | 12 |
+--------+-------+
2 rows in set (0.01 sec)

mysql>

groupby

having

参考サイト:
6.4.1. SELECT 構文