以下のテーブルのような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



CREATE TABLE `LOAD_DATA` (
`ID` int(11) DEFAULT NULL,
`FLAG` char(1) DEFAULT NULL,
`SDATE` timestamp NULL DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

imp_exp

以下のようにNULLと記入されているファイルをLOADすると以下のようになる。
[root@colinux tmp]# cat load.txt
NULL NULL NULL
[root@colinux tmp]#


LOAD DATA INFILE '/tmp/load.txt' INTO TABLE LOAD_DATA LINES TERMINATED BY '\r\n';

テキストの中身が「NULL NULL NULL」の場合
load_data_2

テキストの中身が「\N \N \N」の場合
null1
http://variable.jp/?p=344


UNIQUE インデックスは、インデックス内の全ての値は明確でなければいけないというような制限を作成します。
既存行とマッチするキー値の新しい行を追加しようとするとエラーが発生します。
全てのエンジンに対して、UNIQUE インデックスは NULL を含む事ができるカラムの複数 NULL 値を許容します


mysql> CREATE TABLE `A1` (
-> `comment` varchar(10) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

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

mysql> CREATE UNIQUE INDEX IDX_UQ_A1 ON A1(comment);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc A1;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(10) | YES | UNI | NULL | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql>

unique_idx

※ UNIQUE index will be UNI or PRI if the index does not allow NULL values.
※ MUL if the index does allow NULL values because NULL in a UNIQUE index is a special case:
Multiple NULL values are allowed, unlike any other value.


mysql> desc A1;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(10) | YES | UNI | NULL | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO A1 VALUES (NULL),(NULL),('data'),('test'),(NULL);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from A1;
+---------+
| comment |
+---------+
| NULL |
| NULL |
| NULL |
| data |
| test |
+---------+
5 rows in set (0.00 sec)

mysql> INSERT INTO A1 VALUES('data');
ERROR 1062 (23000): Duplicate entry 'data' for key 'IDX_UQ_A1'
mysql>

unique_idx_null

参考サイト
12.1.7. CREATE INDEX 構文


NULL, NOT NULL, DEFAULT, SET,ENUM,データ型などによる
Default値の変化について。


mysql> CREATE TABLE defaults (
-> id INT UNSIGNED NOT NULL UNIQUE,
-> col1 INT NULL,
-> col2 INT NOT NULL,
-> col3 INT DEFAULT 42,
-> col4 CHAR(5) NULL,
-> col5 CHAR(5) NOT NULL,
-> col6 CHAR(5) DEFAULT 'yoo',
-> col7 TEXT NULL,
-> col8 TEXT NOT NULL,
-> col9 TIME NOT NULL,
-> col10 DATE NULL,
-> col11 DATE NOT NULL,
-> col12 DATE DEFAULT '2002-02-08',
-> col13 ENUM('doo','yoo'),
-> col14 SET('blabla','yooyoo'),
-> col15 ENUM('doo','yoo') NOT NULL,
-> col16 SET('blabla','yooyoo') NOT NULL
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc defaults;
+-------+------------------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------+------+-----+------------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| col1 | int(11) | YES | | NULL | |
| col2 | int(11) | NO | | NULL | |
| col3 | int(11) | YES | | 42 | |
| col4 | char(5) | YES | | NULL | |
| col5 | char(5) | NO | | NULL | |
| col6 | char(5) | YES | | yoo | |
| col7 | text | YES | | NULL | |
| col8 | text | NO | | NULL | |
| col9 | time | NO | | NULL | |
| col10 | date | YES | | NULL | |
| col11 | date | NO | | NULL | |
| col12 | date | YES | | 2002-02-08 | |
| col13 | enum('doo','yoo') | YES | | NULL | |
| col14 | set('blabla','yooyoo') | YES | | NULL | |
| col15 | enum('doo','yoo') | NO | | NULL | |
| col16 | set('blabla','yooyoo') | NO | | NULL | |
+-------+------------------------+------+-----+------------+-------+
17 rows in set (0.01 sec)

64

mysql> INSERT INTO defaults (id) VALUES (1);
Query OK, 1 row affected, 6 warnings (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1364 | Field 'col2' doesn't have a default value |
| Warning | 1364 | Field 'col5' doesn't have a default value |
| Warning | 1364 | Field 'col8' doesn't have a default value |
| Warning | 1364 | Field 'col9' doesn't have a default value |
| Warning | 1364 | Field 'col11' doesn't have a default value |
| Warning | 1364 | Field 'col16' doesn't have a default value |
+---------+------+--------------------------------------------+
6 rows in set (0.00 sec)

mysql> select * from defaults\G
*************************** 1. row ***************************
id: 1
col1: NULL
col2: 0
col3: 42
col4: NULL
col5:
col6: yoo
col7: NULL
col8:
col9: 00:00:00
col10: NULL
col11: 0000-00-00
col12: 2002-02-08
col13: NULL
col14: NULL
col15: doo
col16:
1 row in set (0.00 sec)

mysql>

※データ型やNULL値を許容するかしないかにより値が変わる。
※TEXT型にはDefault値が利用出来ない。
※その他

def64

6.2.3.3. ENUM 型

ENUM 型は、テーブルの作成時にカラムの仕様で明示的に列挙された使用可能な
値のリストから、通常、値が選択される文字列オブジェクトです。

* 不正な値(使用可能な値のリストに含まれない文字列)を ENUM 型のカラムに挿入すると、
特殊なエラー値として、空の文字列が挿入される。この文字列は数値 0 を持つため、’通常’ の空の
文字列とは区別することができる(これについては後述)。

* ENUM 型が NULL として宣言されている場合、そのカラムでは NULL も正しい値となり、
デフォルト値は NULL になる。ENUM 型が NOT NULL として宣言されている場合は、
使用可能な値の最初の要素がデフォルト値として使用される。


TIMESTAMP列はDEFAULTでは、NOT NULLに設定されます。
あらかじめNULL値が入る事を想定している場合は明示的にNULLを
指定してテーブルを作成する。

以下テーブル作成後のALTER TABLEにて属性変更している。


mysql> CREATE TABLE timestamp_null (
-> data_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> i INT
-> );
Query OK, 0 rows affected (0.16 sec)

mysql> desc timestamp_null;

+-----------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+-------------------+-----------------------------+
| data_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| i | int(11) | YES | | NULL | |
+-----------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.01 sec)

mysql> ALTER TABLE timestamp_null
-> MODIFY data_time TIMESTAMP NULL
-> DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc timestamp_null;

+-----------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+-------------------+-----------------------------+
| data_time | timestamp | YES | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| i | int(11) | YES | | NULL | |
+-----------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)

mysql>


mysql> INSERT INTO timestamp_null (data_time, i) VALUES (NULL, 10);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT data_time, i FROM timestamp_null;
+-----------+------+
| data_time | i |
+-----------+------+
| NULL | 10 |
+-----------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO timestamp_null (data_time, i) VALUES (now(), 10);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT data_time, i FROM timestamp_null;
+---------------------+------+
| data_time | i |
+---------------------+------+
| NULL | 10 |
| 2009-07-15 04:31:42 | 10 |
+---------------------+------+
2 rows in set (0.00 sec)

mysql>

null_timestamp

TIMESTAMP関連の検証


mysql> CREATE TABLE timestamp_chk (data_time TIMESTAMP NULL);
Query OK, 0 rows affected (0.00 sec)

mysql> desc timestamp_chk;
+-----------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+---------+-------+
| data_time | timestamp | YES | | NULL | |
+-----------+-----------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> INSERT INTO timestamp_chk VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from timestamp_chk;
+-----------+
| data_time |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)

mysql> INSERT INTO timestamp_chk VALUES ('文字列');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1265 | Data truncated for column 'data_time' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 | ← 文字列は対象外なので"0" Valueがinsertされる。
+---------------------+
2 rows in set (0.00 sec)

mysql>

timestamp_check

日付列に12 Digit と14 DigitのNumberをInsertした時の違い


mysql> INSERT INTO timestamp_chk VALUES (200202082139);
Query OK, 1 row affected (0.01 sec)

mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 |
| 2020-02-02 08:21:39 |
+---------------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO timestamp_chk VALUES (20020208213900);
Query OK, 1 row affected (0.00 sec)

mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 |
| 2020-02-02 08:21:39 |
| 2002-02-08 21:39:00 |
+---------------------+
4 rows in set (0.00 sec)

timestamp_digit

無効な日付をINSERTした場合

    ※2月31日は存在しない日付

mysql> INSERT INTO timestamp_chk VALUES ('2002-02-31 23:59:59');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1265 | Data truncated for column 'data_time' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 |
| 2020-02-02 08:21:39 |
| 2002-02-08 21:39:00 |
| 0000-00-00 00:00:00 |
+---------------------+
5 rows in set (0.00 sec)

    ※60秒が指定されている。

mysql> INSERT INTO timestamp_chk VALUES ('2002-02-28 23:59:60');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1265 | Data truncated for column 'data_time' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 |
| 2020-02-02 08:21:39 |
| 2002-02-08 21:39:00 |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
+---------------------+
6 rows in set (0.00 sec)

mysql>

timestamp_digit_2


NULLとDEFAULT値の動作確認


mysql> CREATE TABLE NULL_DEFAUL (
-> test1 INT NOT NULL,
-> test2 INT NULL,
-> test3 INT NOT NULL DEFAULT 123,
-> test4 INT NULL DEFAULT 456,
-> test5 CHAR(1) NOT NULL DEFAULT '0',
-> test6 VARCHAR(100) NOT NULL DEFAULT 'NO VALUES'
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc NULL_DEFAUL;
+-------+--------------+------+-----+-----------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+-----------+-------+
| test1 | int(11) | NO | | NULL | |
| test2 | int(11) | YES | | NULL | |
| test3 | int(11) | NO | | 123 | |
| test4 | int(11) | YES | | 456 | |
| test5 | char(1) | NO | | 0 | |
| test6 | varchar(100) | NO | | NO VALUES | |
+-------+--------------+------+-----+-----------+-------+
6 rows in set (0.01 sec)

mysql>

default

動作確認


mysql> desc NULL_DEFAUL;
+-------+--------------+------+-----+-----------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+-----------+-------+
| test1 | int(11) | NO | | NULL | |
| test2 | int(11) | YES | | NULL | |
| test3 | int(11) | NO | | 123 | |
| test4 | int(11) | YES | | 456 | |
| test5 | char(1) | NO | | 0 | |
| test6 | varchar(100) | NO | | NO VALUES | |
+-------+--------------+------+-----+-----------+-------+
6 rows in set (0.00 sec)

mysql> insert into NULL_DEFAUL(test1) values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from NULL_DEFAUL;
+-------+-------+-------+-------+-------+-----------+
| test1 | test2 | test3 | test4 | test5 | test6 |
+-------+-------+-------+-------+-------+-----------+
| 1 | NULL | 123 | 456 | 0 | NO VALUES |
+-------+-------+-------+-------+-------+-----------+
1 row in set (0.00 sec)

mysql>

default2