将null插入char(1) [英] Inserting null into char(1)

问题描述

我正在创建INSERT脚本，但是遇到了问题.有一个中间的初始字段，它是char(1)

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

有时记录在该字段中没有任何内容，因此我将其设置为NULL.这将导致数据太长而导致列错误.我不想只输入''，因为这样只会留下空白.

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?

推荐答案

听起来您可能正在尝试将字符串'NULL'插入到char(1)字段中，而不是SQL NULL，并且您具有严格的SQL模式启用，可以防止将其截断为N.

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.

如果可以，请运行

SHOW CREATE TABLE <your_table_name>

在MySQL Shell中按

确定目标字段是否接受NULL.没有上下文(您是将其作为纯SQL运行还是从另一个语言的某个客户端程序连接到数据库?)，很难提供确切的解决方案，但是您可能会遇到类似这样的情况:

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

当您想要的是'NULL'只是一个没有特殊含义的字符串

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

这是一个例子:

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 ...

这篇关于将null插入char(1)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！