user1032531 user1032531 - 1 year ago 55
MySQL Question

MySQL Inserts record with NULL value in NOT NULL column

Why does the first INSERT go through for table2. Note that table2.col_1 is NOT NULL. It doesn't insert NULL for col_1, but mysteriously converts the NULL value to an empty string. I am using MySQL Version 5.5.28. Thanks

mysql> DROP TABLE IF EXISTS table1, table2;

Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS table1 (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
-> col_1 VARCHAR(45) NOT NULL ,
-> col_2 VARCHAR(45) NOT NULL ,
-> PRIMARY KEY (`id`))
-> ENGINE = InnoDB;

Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE table2 LIKE table1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO table1 (id, col_1, col_2) VALUES (NULL, "xxx","yyy");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO table2 (id, col_1, col_2) SELECT NULL, NULL, col_2 FROM table1 WHERE id=1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+-------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------+
| Warning | 1048 | Column 'col_1' cannot be null |
+---------+------+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM table2;
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
| 1 | | yyy |
+----+-------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO table2 (id, col_1, col_2) VALUES( NULL, NULL, "zzz");
ERROR 1048 (23000): Column 'col_1' cannot be null

mysql> SELECT * FROM table2;
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
| 1 | | yyy |
+----+-------+-------+
1 row in set (0.00 sec)

Answer Source

You have MySQL's STRICT mode OFF. Turn it on and you'll get an error.

Otherwise you can test for those warnings with PDO via: http://php.net/manual/en/pdo.errorinfo.php