Mysql关于sql_mode

发表于 | 分类于 | 阅读次数
undefined

啊啊啊啊，一天就这样浪费掉了。本来今天打算开始重构msg的代码。结果发现自己没办法导入前任师兄的mysql文件。于是……花了老半天，以为自己版本太低导致出错了。

一直弄到晚上，把最新版的mysql下了下来。可是居然还是报错，编码问题都解决了。就是错得莫名其妙。恼人得很，去跑了个步。思路瞬间清晰，到命令行跑查出错误的语句后，百度。发现别人也有同样的问题，但是他的问题是int不能设置默认类型。那么，我的timestamp会不会也不能设置默认类型呢？顺着这个思路走下去，最终发现原来是mysql更新后加入sql_mode，也就是有严格和不严格模式。不合格的日期已经不能被允许了。所以要修改sql_mode才能适应以前版本的内容。

但是设置sql_mode并不能在命令行设置，因为这是session级别的内容，每次退出就会重置。（差点就以为我找错方向了）于是找了很多教程，然而好像win7的版本特别奇葩，别人有的文件我都没。找了半天的教程，终于找到一个配置文件的博客！！感谢这位大人。当然，找了那么久，一定要记录下来啊！！！

这份配置不和其他教程一样简短，有很多我不懂的内容。但是最重要的是最后一个句子！！（而且我之前试过在我原来那份简短设置基础上设置，结果也不能改变。所以只能用它的啦）

关于sql_mode还有很多内容。主要是增加了验证功能，让数据库更加优化。

贴上大大的链接：http://www.2cto.com/database/201508/429915.html

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
default-character-set=utf8
[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 = "e:\mysql"
datadir = "e:\mysql\data"
tmpdir = "e:\mysql\data"
socket = "e:\mysql\data\mysql.sock"
port = 3306
log-error = "e:\mysql\data\mysql_error.log"
# server_id = .....
max_connections = 100
table_open_cache = 256
query_cache_size = 1M
tmp_table_size = 32M
thread_cache_size = 8
innodb_data_home_dir = "D:\dev_tools\mysql-5.7.7\data\"
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 128M
innodb_buffer_pool_size = 128M
innodb_log_file_size = 10M
innodb_thread_concurrency = 16
innodb-autoextend-increment = 1000
# 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 = 32M
read_rnd_buffer_size = 32M
max_allowed_packet = 32M
explicit_defaults_for_timestamp = true
sql_mode=""