数据库工作笔记001---mysql 修改字符集_修改排序规则

  JAVA技术交流QQ群：170933152 

用到了,就整理出来,下次查阅

看看下面这个比较好:

创建数据库时候设置编码:

gbk: create database '数据库名' default character set gbk collate gbk_chinese_ci; 
utf8: create database `test2` default character set utf8 collate utf8_general_ci

 

命令设置编码:

set character_set_client = utf8; 
set character_set_connection = utf8; 
set character_set_database = utf8; 
set character_set_results = utf8;/*这里要注意很有用*/ 
set character_set_server = utf8;

set collation_connection = utf8_bin; 
set collation_database = utf8_bin; 
set collation_server = utf8_bin;

 

排序规则：是指对指定字符集下不同字符的比较规则。其特征有以下几点：

1、 两个不同的字符集不能有相同的排序规则

2、 两个字符集有一个默认的排序规则

3、 有一些常用的命名规则。如_ci结尾表示大小写不敏感（caseinsensitive）,_cs表示大小写敏感（case sensitive）,_bin表示二进制的比较（binary）. utf-8有默认的排序规则： 命令：SHOW CHARSET LIKE 'utf8%'; 区别：   utf8_general_ci 不区分大小写，这个你在注册用户名和邮箱的时候就要使用。 utf8_general_cs 区分大小写，如果用户名和邮箱用这个 就会照成不良后果 utf8_bin:字符串每个字符串用二进制数据编译存储。 区分大小写，而且可以存二进制的内容   utf8_general_ci校对速度快，但准确度稍差。 utf8_unicode_ci准确度高，但校对速度稍慢。 

一、修改my.ini配置文件（mysql配置文件）

character_set_server = utf8 #设置字符集

重启mysql数据库服务

查看当前数据库字符集

show VARIABLES like 'character%';

二、修改数据库字符集

alter database 数据库名 character set utf8;

ps:修改完数据库字符集，需要重启mysql数据库。

三、修改表字符集

ALTER TABLE  表名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

生成所有表修改字符集语句：

SELECT TABLE_NAME,CONCAT('ALTER TABLE  ',TABLE_NAME,' DEFAULT CHARACTER SET ',a.DEFAULT_CHARACTER_SET_NAME,' COLLATE ',a.DEFAULT_COLLATION_NAME,';') executeSQL FROM information_schema.SCHEMATA a,information_schema.TABLES b
WHERE a.SCHEMA_NAME=b.TABLE_SCHEMA
AND a.DEFAULT_COLLATION_NAME!=b.TABLE_COLLATION
AND b.TABLE_SCHEMA='数据库名' 

四、修改列字符集

ALTER TABLE  表名 CHANGE  列名  列名  VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

生成所有列修改字符集语句：

select b.table_name,b.column_name,b.character_set_name,b.collation_name
,CONCAT('ALTER TABLE ',b.table_name,' MODIFY ',b.column_name,' ',b.DATA_TYPE,'(',b.CHARACTER_MAXIMUM_LENGTH,') ',CASE WHEN b.COLUMN_DEFAULT IS NULL THEN ''  ELSE CONCAT('DEFAULT \'',b.COLUMN_DEFAULT,'\'') END,' COMMENT \'',b.COLUMN_COMMENT,'\';') executeSQL
 from information_schema.TABLES a,information_schema.COLUMNS b where  b.character_set_name IS NOT NULL and a.TABLE_SCHEMA=b.TABLE_SCHEMA AND a.TABLE_NAME=b.TABLE_NAME
AND a.TABLE_COLLATION!=b.COLLATION_NAME
and a.TABLE_SCHEMA='数据库名'

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

由于数据库中的数据表和表字段的字符集和排序规则不统一，找了很多帖子，最后发现如下脚本很好用。 用法儿是：先执行如下脚本生成修改数据表和表字段的脚本，然后再执行这些生成的脚本。

1. 修改指定数据库中所有varchar类型的表字段的字符集为UTF8，并将排序规则修改为utf8_general_ci

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET UTF8 COLLATE utf8_general_ci', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'databaseName' AND DATA_TYPE = 'varchar' AND (     CHARACTER_SET_NAME != 'utf8'     OR     COLLATION_NAME != 'utf8_general_ci' );   2. 修改指定数据库中所有数据表的字符集为UTF8，并将排序规则修改为utf8_general_ci

SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET  utf8 COLLATE utf8_unicode_ci;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'databaseName' ---------------------