mysql 5.7.17 字符集设置_mysql(5.7.17)字符集设置(character_set/collation)

0 查看字符集(character_set/collation)

use information_schema;

desc tables;    --一定记住tables表，information_schema中的metadata都可以查到

-- 查看db的默认，DEFAULT_CHARACTER_SET_NAME/DEFAULT_COLLATION_NAME

select * from schemata where schema_name = ‘db_name‘;

-- 查看table的，只有table_collation字段

select * from tables where table_schema = ‘db_name‘ and table_name = ‘table_name‘;

-- 查看column的，CHARACTER_SET_NAME/COLLATION_NAME都要

select * from columns where table_schema = ‘db_name‘ and table_name = ‘table_name‘;

1

2

3

4

5

6

7

8

9

10

11

--查看mysql支持的character_set，Default collation是默认的"大小写敏感"

select * from information_schema.character_sets;

--就是执行了上面的sql

show charset;

1

2

3

4

/*

查看各character set的collation，以gbk为例

gbk_chinese_ci是"大小写不敏感"，ci是case sensitive的缩写

gbk_bin是"大小写敏感"，以二进制(binary)存储

*/

select * from information_schema.collations;

show collation;

1

2

3

4

5

6

7

show variables like ‘character_set%‘;   --查看当前环境的character_set

1

show variables like ‘collation%‘;   --查看当前的collation

1

1 my.ini配置character_set

-- 注意位置，放在[client]和[mysql]都可以，效果相同，以后面的为准。放在[mysqld]下服务不能启动。

default-character-set = utf8

-- 上句设置之后，对应的全局变量改变

character_set_client = utf8

character_set_connection = utf8

character_set_results = utf8

-- 注意位置，放在[mysqld]下

character-set-server = gbk

collation-server = gbk_bin

-- 上句设置之后，对应的全局变量改变

character_set_database = gbk

character_set_server = gbk    --默认的create database db时的character_set，其他的含义待测试，可根据字面单词猜想

collation_database = gbk_bin

collation_server = gbk_bin

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

2 数据库的character_set指定/修改

-- 默认db的character_set和@@character_set_server一致，table和db一致，column和table一致

create database db;

-- defalut可有可无，注意gbk的default collation(上面有)是gbk_chinese_ci(大小写不敏感)

-- 即使collation_database/server都是gbk_bin，创建的db默认仍是gbk_chinese_ci，这里专门指定

create datadbase db character set gbk collate gbk_bin;

create datadbase db default character set gbk collate gbk_bin;  --正确

alter datadbase db character set gbk collate gbk_bin;       --正确

alter datadbase db convert to character set gbk collate gbk_bin;    --错误，不支持convert to

1

2

3

4

5

6

7

8

9

10

11

3 table的character_set指定/修改

create table t1(c1 varchar2) character set gbk collate gbk_chinese_ci;

-- 测试

insert into t1 values(‘a‘);

select * from t1 where c1 = ‘A‘;    --返回‘a‘，此时‘a‘=‘A‘

--注意以下两种方法的差别，第1句是只是修改默认

alter table t1 character set gbk collate gbk_bin;   --提示Records: 0

--测试

alter table t1 add c2 varchar(50);

insert into t1 values(‘b‘,‘b‘);

select * from t1 where c1 = ‘A‘;    --返回‘a‘，c1仍然是gbk_chinese_ci

select * from t1 where c2 = ‘B‘;    --返回empty，c2是gbk_bin

--已有字段c1,c2及再添加的默认都是gbk_bin

alter table t1 convert to character set gbk collate gbk_bin;    --提示Records: 1

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

4 column的character_set指定/修改

-- 套路相同

alter table add c1 varchar(50) character set gbk collate gbk_bin;

alter table t1 change c1 c1 varchar(50) character set gbk collate gbk_bin;

---------------------

作者：苦笔

来源：CSDN

原文：https://blog.csdn.net/u014711094/article/details/78821809

版权声明：本文为博主原创文章，转载请附上博文链接！