php 如何在 MySQL 中允许整数为空字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28606483/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to allow empty string for integer in MySQL?
提问by ilhan
I have integer fields in a table. The POSTs are send by a complicated JavaScript. They send empty string like "" but as you guess MySQL doesn't allow emty strings in integer fields. Is there any option to allow empty strings? Like if it takes empty string it will save it as NULL.
我在表中有整数字段。POST 由复杂的 JavaScript 发送。他们发送像 "" 这样的空字符串，但正如您猜测的那样，MySQL 不允许整数字段中的空字符串。有没有允许空字符串的选项？就像如果它需要空字符串一样，它会将其保存为 NULL。
采纳答案by ilhan
Removing sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"from my.ini has solved the issue.
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"从 my.ini 中删除已经解决了这个问题。
Edit: Removing the line above works but it is a bad idea. It allows to have things like 0000-00-00 or empty string dates. Better keep the line above and don't insert empty sting into an integer field, instead convert empty string into NULL and then insert that NULL into integer field.
编辑：删除上面的行有效，但这是一个坏主意。它允许有诸如 0000-00-00 或空字符串日期之类的东西。最好保留上面的行，不要将空字符串插入整数字段，而是将空字符串转换为 NULL，然后将该 NULL 插入整数字段。
回答by Milan Malani
There are 2 ways to do this.
有两种方法可以做到这一点。
- For Current Mysql Session (Temporary Solution)
- 对于当前的 Mysql 会话（临时解决方案）
First execute query to get current SQL mode of your mysql server.
首先执行查询以获取 mysql 服务器的当前 SQL 模式。
mysql> SELECT @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------+
|STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
If result contains STRICT_TRANS_TABLES, you have to remove that value to allow insert query to pass NULL value. Make sure your mysql User have privileges to apply this changes and restart Mysql Server after applying this.
如果结果包含STRICT_TRANS_TABLES，则必须删除该值以允许插入查询传递 NULL 值。确保您的 mysql 用户有权应用此更改并在应用此更改后重新启动 Mysql Server。
SET GLOBAL sql_mode = '';
- For Life Time of Mysql (Permanent Solution)
- Mysql 的生命周期（永久解决方案）
You have to update my.cnf file. Location of that file is : \etc\my.cnf or \etc\mysql\mysql.cnf
您必须更新 my.cnf 文件。该文件的位置是：\etc\my.cnf 或 \etc\mysql\mysql.cnf
There will be some default parameters set under [mysqld] like
[mysqld]下会设置一些默认参数，比如
[mysqld]
innodb_file_per_table=1
default-storage-engine=MyISAM
performance-schema=0
max_allowed_packet=268435456
open_files_limit=10000
Just add one line under that
只需在其下添加一行
sql-mode=""
Make sure to restart Mysql Server after changing this file. Normally root user will be the owner of file so you have to login with root user on server.
更改此文件后，请确保重新启动 Mysql Server。通常 root 用户将是文件的所有者，因此您必须在服务器上使用 root 用户登录。
For more details to understand what this SQL mode do.
有关更多详细信息以了解此 SQL 模式的作用。
STRICT_TRANS_TABLES
STRICT_TRANS_TABLES
Enable strict SQL mode for transactional storage engines, and when possible for non-transactional storage engines. For details, see Strict SQL Mode.
为事务存储引擎启用严格的 SQL 模式，并在可能的情况下为非事务存储引擎启用。有关详细信息，请参阅严格 SQL 模式。
Refer : http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_strict_trans_tables
参考：http: //dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_strict_trans_tables
NO_AUTO_CREATE_USER
NO_AUTO_CREATE_USER
Prevent the GRANT statement from automatically creating new user accounts if it would otherwise do so, unless authentication information is specified. The statement must specify a nonempty password using IDENTIFIED BY or an authentication plugin using IDENTIFIED WITH.
除非指定了身份验证信息，否则阻止 GRANT 语句自动创建新用户帐户，否则会这样做。该语句必须使用 IDENTIFIED BY 或使用 IDENTIFIED WITH 的身份验证插件指定非空密码。
Refer: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_auto_create_user
参考：http: //dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_auto_create_user
NO_ENGINE_SUBSTITUTION
NO_ENGINE_SUBSTITUTION
Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in.
当 CREATE TABLE 或 ALTER TABLE 等语句指定禁用或未编译的存储引擎时，控制默认存储引擎的自动替换。
Refer : http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_engine_substitution
参考：http: //dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_engine_substitution
回答by BenM
Assuming that the column allows for NULLvalues, you must explicitly tell MySQL to use a value of NULL, rather than passing an empty string (which is cast to 0):
假设该列允许NULL值，您必须明确告诉 MySQL 使用值NULL，而不是传递空字符串（转换为0）：
INSERT INTO table (column_name) VALUES (NULL);
