Mysql5.6.20 database sql_mode schema settings
The development requirement requires that the data be imported into a table in a library in mysql through the php daemon, but an error is reported: Incorrect integer value: 'for column' id' at row 1. At the same time, I insert into a piece of data, insert into log values (', 'admin','31','002t') in this table in the mysql5.6 database, or report an error with the same error as above.
So online Baidu found a solution:
Reference: http://blog.csdn.net/j2eelamp/article/details/6866614
Solution:
And then I checked the MySQL file. It is found that if the version above 5 is null, you should write NULL.
This problem usually occurs on mysql 5.x. I use mysql5.1.
The official explanation: knowing that the new version of mysql has "bug" for null inserts, remove the enable strict SQL mode checked by default when installing mysql.
At the same time, the version of my online mysql database is mysql5.6.20.
So I found a document:
Mysql5.6 sql_mode Settin
Reference:
Due to the needs of the data staff, we now need to modify the sql_mode of mysql
Sql_mode defaults to sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
At this time, I configure the following in / etc/my.cnf
Sql_mode=NO_ENGINE_SUBSTITUTION
After reboot or
Mysql > SELECT @ @ GLOBAL.sql_mode
+-- +
| | @ @ GLOBAL.sql_mode |
+-- +
| | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+-- +
1 row in set (0.00 sec)
This is because there is a file called my.cnf under the directory where I compiled and installed, but this file contains the item sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES.
Put this my.cnf mv my.cnf my.cnf.ori
Grep sql_mode / usr/local/mysql/my.cnf.ori
Sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
Also set in the / etc/my.cnf file:
Sql_mode= NO_AUTO_Create_USER,NO_ENGINE_SUBSTITUTION
Grep "sql_mode" / etc/my.cnf
Sql_mode=NO_AUTO_Create_USER,NO_ENGINE_SUBSTITUTION
Restart mysql
Mysql > SELECT @ @ GLOBAL.sql_mode
+-- +
| | @ @ GLOBAL.sql_mode |
+-- +
| | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-- +
At this point, the developer imports the data into the background again and no longer reports an error.
