以下のテーブルのようなENUM列がある列にNOT NULLを設定しPKを設定
した場合にどのくらいのデータが入るか確認。


CREATE TABLE `Country001` (
`Code` char(2) NOT NULL DEFAULT '',
`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
PRIMARY KEY (`Continent`)
) ENGINE=MyISAM DEFAULT CHARSET=UTF8;

mysql> CREATE TABLE `Country001` (
-> `Code` char(2) NOT NULL DEFAULT '',
-> `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
-> PRIMARY KEY (`Continent`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=UTF8;

Query OK, 0 rows affected (0.01 sec)

mysql> desc Country001;
+———–+—————————————————————————————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———–+—————————————————————————————+——+—–+———+——-+
| Code | char(2) | NO | | | |
| Continent | enum(‘Asia’,’Europe’,’North America’,’Africa’,’Oceania’,’Antarctica’,’South America’) | NO | PRI | Asia | |
+———–+—————————————————————————————+——+—–+———+——-+
2 rows in set (0.01 sec)

mysql> insert into Country001 (Code,Continent) values('AAA','Asia');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into Country001 (Code,Continent) values('EEE','Europe');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into Country001 (Code,Continent) values('NNN','North America');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into Country001 (Code,Continent) values('NNN','Africa');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into Country001 (Code,Continent) values('NNN','Oceania');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into Country001 (Code,Continent) values('NNN','Antarctica');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into Country001 (Code,Continent) values('AAA','South America');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into Country001 (Code,Continent) values('AAA','South A');
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql>

country001

下記の列には、PKがありNULLを許容していないのでENUMのデータ7件とエラー時の空文字が入るので、
データとしては、最大8行のデータが入る。(ENUM+空文字)
enum(‘Asia’,’Europe’,’North America’,’Africa’,’Oceania’,’Antarctica’,’South America’)

country001_1

8行のデータ以外はもうInsertする事が出来ません。これは、PKの設定をせず、NULL
許容していないのでUNIQUE KEYをPK代わりに利用しても同じ結果になる。
t_enum

ストレージエンジンによる結果の違い

以下のINSERTはENUMに無い値を2ついれようとしているので、
空文字に変換された値が2つありPKエラーになっています。


CREATE TABLE `T_enum` (
`Col` enum('first','second','third') NOT NULL DEFAULT 'first',
PRIMARY KEY (`Col`)
) ENGINE=MyISAM DEFAULT CHARSET=UTF8;

MYISAM(エラーまでインサートされます)
enum


CREATE TABLE `T_enum` (
`Col` enum('first','second','third') NOT NULL DEFAULT 'first',
PRIMARY KEY (`Col`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

INNODB(トランザクション処理なので、エラーで処理がROLLBACKされてます)
enum_innodb


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