mysql char null,将null插入char（1）

最新推荐文章于 2024-04-25 16:08:59 发布

杨大宝识

最新推荐文章于 2024-04-25 16:08:59 发布

阅读量376

点赞数

文章标签： mysql char null

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?

解决方案

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

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

SELECT first_name, 'NULL', last_name

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

INSERT

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;