Tuesday, July 29, 2008

why are there empty strings in my not null column?

So you're looking at your database and wondering wtf are those default values doing in there?
mysql> create table foo(title char(10) not null);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into foo(title) values (NULL);
ERROR 1048 (23000): Column 'title' cannot be null

that was expected...

mysql> insert into foo(title) values (NULL),("OH SNAP");
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 1

mysql> show warnings;
+---------+------+-------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------+
| Warning | 1048 | Column 'title' cannot be null |
+---------+------+-------------------------------+
1 row in set (0.00 sec)
Hmm, not quite what I was expecting...error became a warning..

mysql> select * from foo;
+-----------+
| title
+-----------+
| asdf
|
| OH SNAP
+-----------+
3 rows in set (0.00 sec)
we get the default empty string inserted for us...how nice

mysql> set sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@session.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into foo(title) values (NULL),("asdf");
ERROR 1048 (23000): Column 'title' cannot be null
woot, stricter now..

A

No comments: