mysql(5.7.17)字符集设置(character_set/collation)
来源:互联网 发布:赤峰市用友软件销售 编辑:程序博客网 时间:2024/05/29 16:42
0 查看字符集(character_set/collation)
use information_schema;desc tables; --一定记住tables表,information_schema中的metadata都可以查到-- 查看db的默认,DEFAULT_CHARACTER_SET_NAME/DEFAULT_COLLATION_NAMEselect * 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';
--查看mysql支持的character_set,Default collation是默认的"大小写敏感"select * from information_schema.character_sets; --就是执行了上面的sqlshow charset;
/*查看各character set的collation,以gbk为例gbk_chinese_ci是"大小写不敏感",ci是case sensitive的缩写gbk_bin是"大小写敏感",以二进制(binary)存储*/select * from information_schema.collations;show collation;
show variables like 'character_set%'; --查看当前环境的character_set
show variables like 'collation%'; --查看当前的collation
1 my.ini配置character_set
-- 注意位置,放在[client]和[mysql]都可以,效果相同,以后面的为准。放在[mysqld]下服务不能启动。default-character-set = utf8-- 上句设置之后,对应的全局变量改变character_set_client = utf8character_set_connection = utf8 character_set_results = utf8-- 注意位置,放在[mysqld]下character-set-server = gbkcollation-server = gbk_bin-- 上句设置之后,对应的全局变量改变character_set_database = gbk character_set_server = gbk --默认的create database db时的character_set,其他的含义待测试,可根据字面单词猜想collation_database = gbk_bincollation_server = gbk_bin
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
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_ciselect * from t1 where c2 = 'B'; --返回empty,c2是gbk_bin--已有字段c1,c2及再添加的默认都是gbk_binalter table t1 convert to character set gbk collate gbk_bin; --提示Records: 1
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;
阅读全文
0 0
- mysql(5.7.17)字符集设置(character_set/collation)
- MYSQL 字符集的设置(命令行模式设置character_set*)
- MYSQL 字符集的设置(命令行模式设置character_set*)
- MySQL字符集,Collation
- mysql collation 字符集校对
- MySQL字符集的校对规则(collation rule)
- MySql: charset 和 collation的设置
- MySql:charset和collation的设置
- mysql的collation区分大小写设置
- 字符集校对规则collation
- MySQL collation
- MySQL collation
- mysql collation
- MySQL的字符集支持(Character Set Support)有两个方面:字符集(Character set)和排序方式(Collation)。
- Mysql(5.7)设置字符集为UTF-8
- MySQL 5.7.18 设置数据库字符集
- mysql 字符集的设置
- 深入Mysql字符集设置
- 学习python第一步
- Windows在Anaconda下安装Tensorflow Keras 支持Python3.6
- client-go的使用及源码分析
- Javascript之DOM简单编程
- Encrypt-then-Mac
- mysql(5.7.17)字符集设置(character_set/collation)
- 《Spark SQL大数据实例开发》9.2 综合案例实战——电商网站搜索排名统计
- 让你看懂聚类分析
- web.xml 中的 servlet 和 servlet-mapping 标签
- httpClient入门到精通-------连接池的关闭
- kinect v2_bodyindex
- leetcode 525. Contiguous Array 统计1和0数量相等的最长子数组
- C++之cmath常用库函数一览
- 1136. A Delayed Palindrome (20)