Inserting null into char(1)

Question

I am creating a INSERT script and I am coming across a problem. There is a middle initial field that is a char(1)

Sometimes the records don't have anything in that field so I put a NULL. This causes a Data too long for column error. I don't want to just put ' ' as that leaves just a blanks space.

Is there another way around this?

Answer 1

It sounds like you may be attempting to insert the string 'NULL' into the char(1) field, rather than an SQL NULL, and you have strict SQL mode enabled, which prevents this being truncated to N.

If you are able, run

SHOW CREATE TABLE <your_table_name>

in the MySQL shell to determine whether your target field accepts NULLs. With no context (are you running this as pure SQL, or connecting to the database from some client program in another langauge?) it's difficult to provide the exact solution, but you may have something like this:

INSERT <your_table_name>
SELECT first_name, 'NULL', last_name

where 'NULL' is simply a string with no special meaning, when what you intend is

INSERT <your_table_name>
SELECT first_name, NULL, last_name

Here's an illustration:

mysql> CREATE TABLE my_table ( middle_initial CHAR(1) NULL );
mysql> INSERT INTO my_table SELECT 'NULL';
mysql> SHOW WARNINGS;

Level       Code    Message
Warning     1265    Data truncated for column 'middle_initial' at row 1

mysql> SELECT * FROM my_table;

middle_initial
--------------
N

mysql> set sql_mode = 'STRICT_TRANS_TABLES';

mysql> INSERT INTO my_table SELECT 'NULL';

ERROR 1406 (22001) at line 16: Data too long for column 'middle_initial' at row 1

mysql> INSERT INTO my_table SELECT NULL;
mysql> SELECT * FROM my_table;

middle_initial
--------------
N
NULL

Bit of a punt - apologies if no use ...