MySQL 5.6中的sql_mode默认设置问题

最新推荐文章于 2024-01-30 11:32:20 发布

醉醚红色

最新推荐文章于 2024-01-30 11:32:20 发布

阅读量3.1k

点赞数

分类专栏： MySQL 文章标签： mysql 数据库服务器

本文链接：https://blog.csdn.net/micahriven/article/details/12030981

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

编译并配置完MySQL5.6版本，导入之前在MySQL5.5中正常运行的数据库却发生错误，原来从MySQL5.6版本开始，在执行mysql_install_db命令时（该命令一般在编译完mysql后运行并对数据库进行初始配置)会将默认的一个my.cnf文件写入到安装路径中(CentOS下默认安装路径为/usr/local/mysql/），其包含的信息如下：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# 作者按: 见这里的配置
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

上面的sql_mode中包含有STRICT_TRANS_TABLES，这就是导致文章开头所述问题发生的原因。在将sql_mode一行注释后重新启动mysql服务一切恢复正常。