NULL 與 NOT NULL

將 NULL 和’bad-value’儲存到可空列和非可空列時會發生什麼的示例。還通過+0 顯示了對數字的轉換用法。

CREATE TABLE enum (
    e     ENUM('yes', 'no')   NOT NULL,
    enull ENUM('x', 'y', 'z')     NULL
        );
INSERT INTO enum (e, enull)
    VALUES
        ('yes', 'x'),
        ('no',  'y'),
        (NULL,  NULL),
        ('bad-value', 'bad-value');
Query OK, 4 rows affected, 3 warnings (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 3

mysql>SHOW WARNINGS;
+---------+------+--------------------------------------------+
| `Level`   | Code | Message                                    |
+---------+------+--------------------------------------------+
| `Warning` | 1048 | Column 'e' cannot be null                  | 
| `Warning` | 1265 | Data truncated for column 'e' at row 4     |
| `Warning` | 1265 | Data truncated for column 'enull' at row 4 |
+---------+------+--------------------------------------------+
3 rows in set (0.00 sec)

這些插入後的表格中有什麼。這使用“+0”轉換為數字，檢視儲存的內容。

mysql>SELECT e, e+0 FROM enum;
+-----+-----+
| `e`   | e+0 |
+-----+-----+
| `yes` |   1 |
| `no`  |   2 |
|     |   0 |  -- NULL
|     |   0 |  -- 'bad-value'
+-----+-----+
4 rows in set (0.00 sec)

mysql>SELECT enull, enull+0 FROM enum;
+-------+---------+
| `enull` | enull+0 |
+-------+---------+
| `x`     |       1 |
| `y`     |       2 |
| `NULL`  |    NULL |
|       |       0 |  -- 'bad-value'
+-------+---------+
4 rows in set (0.00 sec)