修改及查看mysql数据库的字符集
操作系统:Windows Server 2008 R2
Mysql版本:mysql-5.6.29-winx64
事由:MySQL5.6的修改字符集编码为UTF8(解决中文乱码问题)
一、登录MySQL查看用SHOW VARIABLES LIKE ‘character%’;下字符集,显示如下:
|
|
修改完成后,service mysql restart重启mysql服务就生效。注意:[mysqld]字段与[mysql]字段是有区别的。这点在网上没人反馈过。
使用SHOW VARIABLES LIKE ‘character%’;查看,发现 数据库编码全已改成utf8。
|
附My.ini 配置文件内容:
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/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.
[WinMySQLAdmin]
#指定mysql服务启动的启动文件
Server=D:\MySQL\Mysql-Windows\mysql-5.6.29-winx64\bin\mysql.exe[mysql]
no-auto-rehash
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 = D:\MySQL\Mysql-Windows\mysql-5.6.29-winx64
datadir = D:\MySQL\Mysql-Windows\mysql-5.6.29-winx64\data
port = 3306
server_id = 1
# default-character-set=gbk
#default-character-set=utf8
character-set-server=utf8
# 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 [client]
#设置mysql客户端的字符集
#default-character-set=gbk
default-character-set=utf8
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
//-----------------------------------//
mysql的sql_mode 模式
1. sql_mode模式
mysql数据库的中有一个环境变量sql_mode,定义了mysql应该支持的sql语法,数据校验等!我们可以通过以下方式查看当前数据库使用的sql_mode:
[java] view plain copy
1. mysql> select @@sql_mode;
2. +----------------------------------------------------------------+
3. | @@sql_mode |
4. +----------------------------------------------------------------+
5. | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
6. +----------------------------------------------------------------+
或者通过查看系统变量方式:
[java] view plain copy
1. mysql> show variables like 'sql_mode%'\G;
2. *************************** 1. row ***************************
3. Variable_name: sql_mode
4. Value: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
mysql5.0以上版本支持三种sql_mode模式:ANSI、TRADITIONAL和STRICT_TRANS_TABLES。
ANSI模式:宽松模式,对插入数据进行校验,如果不符合定义类型或长度,对数据类型调整或截断保存,报warning警告。
TRADITIONAL模式:严格模式,当向mysql数据库插入数据时,进行数据的严格校验,保证错误数据不能插入,报error错误。用于事物时,会进行事物的回滚。
STRICT_TRANS_TABLES模式:严格模式,进行数据的严格校验,错误数据不能插入,报error错误。
1.2 ANSI模式
[java] view plain copy
1. mysql> set @@sql_mode=ANSI;
2. Query OK, 0 rows affected (0.00 sec)
3.
4. mysql> create table test(name varchar(4), pass varchar(4));
5. Query OK, 0 rows affected (0.03 sec)
6.
7. mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');
8. Query OK, 2 rows affected, 2 warnings (0.02 sec)
9. Records: 2 Duplicates: 0 Warnings: 2
10.
11. mysql> show warnings;
12. +---------+------+-------------------------------------------+
13. | Level | Code | Message |
14. +---------+------+-------------------------------------------+
15. | Warning | 1265 | Data truncated for column 'name' at row 1 |
16. | Warning | 1265 | Data truncated for column 'pass' at row 1 |
17. +---------+------+-------------------------------------------+
18. 2 rows in set (0.00 sec)
19.
20. mysql> select * from test;
21. +------+------+
22. | name | pass |
23. +------+------+
24. | aaaa | aaaa |
25. | bbbb | bbbb |
26. +------+------+
27. 2 rows in set (0.00 sec)
我们可以看到,在ANSI模式下,当我们插入数据时,未满足列长度要求时,数据同样会插入成功,但是对超出列长度的字段进行截断,同时报告warning警告。
1.3 STRICT_TRANS_TABLES模式
[java] view plain copy
1. mysql> set @@sql_mode=STRICT_TRANS_TABLES;
2. Query OK, 0 rows affected (0.00 sec)
3.
4. mysql> create table test(name varchar(4), pass varchar(4));
5. Query OK, 0 rows affected (0.02 sec)
6.
7. mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');
8. ERROR 1406 (22001): Data too long for column 'name' at row 1
9.
10. mysql> show errors;
11. +-------+------+------------------------------------------+
12. | Level | Code | Message |
13. +-------+------+------------------------------------------+
14. | Error | 1406 | Data too long for column 'name' at row 1 |
15. +-------+------+------------------------------------------+
16. 1 row in set (0.00 sec)
17.
18. mysql> select * from test;
19. Empty set (0.00 sec)
我们可以看到,在STRICT_TRANS_TABLES模式下,当我们插入数据时,mysql会严格的进行数据的校验,当发现插入列值未满足要求,直接报告error错误,保证了错误数据无法插入到数据库中。
1.3 TRADITIONAL模式
[java] view plain copy
1. mysql> set @@sql_mode=TRADITIONAL;
2. Query OK, 0 rows affected (0.00 sec)
3.
4. mysql> create table test(name varchar(4), pass varchar(4));
5. Query OK, 0 rows affected (0.02 sec)
6.
7. mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');
8. ERROR 1406 (22001): Data too long for column 'name' at row 1
9.
10. mysql> show errors;
11. +-------+------+------------------------------------------+
12. | Level | Code | Message |
13. +-------+------+------------------------------------------+
14. | Error | 1406 | Data too long for column 'name' at row 1 |
15. +-------+------+------------------------------------------+
16. 1 row in set (0.00 sec)
17.
18. mysql> select * from test;
19. Empty set (0.00 sec)
TRADITIONAL模式与STRICT_TRANS_TABLES模式执行的结果,在这种情况下一致。
[java] view plain copy
1. mysql> select @@sql_mode\G;
2. *************************** 1. row ***************************
3. @@sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,E
4. RROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
5. 1 row in set (0.00 sec)
看一下TRADITIONAL模式,我们发现在TRADITIONAL模式下,对所有的事务存储引擎,非事务存储引擎检查,日期类型中的月和日部分不能包含0,不能有0这样的日期(0000-00-00),数据不能除0,禁止grant自动创建新用户等一些校验。
注意:我们这里设置的sql_mode都是session级别的。另外,可以直接修改my.ini文件,找到sql_mode,然后设置新的模式即可!
例如:
vi /etc/my.cnf
在[mysqld]下面添加如下列:
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES