09 MySQL字符集

字符集的选择
    1.数据库方面最流行的是UTF-8
    2.如果只考虑支持汉字,那么使用GBK,毕竟GBK下,每个汉字只占用2个字节,而UTF-8需要3个字节.
    3.如果需要做大量的字符运算,如比较/排序等,选择 定长字符集 比较好.
    4.如果所有客户端程序都支持相同的字符集,优先选择该字符集.避免因字符集转换带来的性能开销以及数据损失.
    
    
MySQL支持的字符集简介:
    MySQL服务器可以支持多种字符集.同一台服务器,同一个数据库,甚至同一个表中的不同字段都可以指定不同的字符集.(Oracle同一个数据库只能使用相同的字符集)
 
    查看所有可用的字符集:
        SHOW CHARACTER SET ;
    或者查看information_schema.character_sets表
        DESC information_schema.character_sets ;
 
    查看字符集所对应的校对规则(一对多关系)
        SHOW COLLATION LIKE '***' ; -- 如LIKE 'gbk%'
        校对规则的命名约定 :
            字符集名[_语言名]_后缀
        后缀包括: ci(大小写不敏感) , cs(大小写敏感) , bin(基于二进制比较,和语言本身无关).
        例如: gbk_chinese_ci , gbk_bin ...
 
        SELECT
            CASE WHEN 'A'  COLLATE gbk_chinese_ci = 'a'
            THEN
                1
            ELSE
                0
            END ;
        --结果返回 1
 
 
MySQL 字符集设置:
    字符集和校对规则有4个级别的默认设置:服务器级,数据库级,表级,字段级(列级).
 
    a.服务器字符集和校对,在MySQL服务启动的时候确定.
     1).在my.cnf中设置
          [mysqld]
          default-character-set=gbk
 
     2).在启动选项中指定
          mysqld --default-character-set= gbk
 
     3).在编译的时候指定 todo how?
          ./configure --with-charset=gbk
 
      如果没有指定服务器的字符集,则默认使用latin1作为服务器的字符集.
      以上三种方式都只指定了字符集,并未指定校对规则.
      查询当前服务器的字符集和校对规则:
        SHOW VARIABLES LIKE 'character_set_server'; --查看服务器字符集
        SHOW VARIABLES LIKE 'collation_server' ;   --查看服务器字符集对应的校对规则
 
    b.数据库级字符集和校对规则
        可以在创建数据库的时候指定,也可以通过ALTER DATABASE 语句进行修改.
        但是采用ALTER TABLE 方式时 : 如果该数据库中已经有数据, 更改字符集并不会对已经存在的数据进行转换,需要使用其他方法.
 
        指定了字符集和校对规则,则使用它们;
        指定了字符集,没有指定校对规则,使用默认校对规则;
        没有指定字符集和校对规则,使用服务器级别的字符集和校对规则.
 
        推荐在创建数据库的时候明确指定字符集和校对规则,避免收到默认值的影响.
 
        查看当前数据库的字符集和校对规则:
            SHOW VARIABLES LIKE 'charcater_set_database' ;
            SHOW VARIABLES LIKE 'collation_database' ;
 
 
    c.表级字符集和校对规则
        推荐在创建表的时候明确指定字符集和校对规则.
        CREATE TABLE t (
            id BIGINT(10) NOT NULL AUTO_INCREAMENT
            , PRIMARY KEY(id)
        ) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_bin ;
        使用SHOW CREATE TABLE tbl_name 语句查看.
 
    d.列的字符集和校对规则
        这种遇到的几率很小.
 
 
 
连接字符集和校对规则: 指客户端与服务器的连接
    MySQL 提供了3个参数,分别是character_set_client , character_set_connection以及character_set_results
    一般来讲这三个需要保持一致,才可以保证用户写入的数据能被正确的读出.
 
    因此,不会单独设置这三个参数,而是一起设置,通过以下命令.
    SET NAMES *** ; --这种方式,需要应用每次连接数据库都要执行.
    更简单的方式,在my.cnf中设置
    [mysqld]
    default-character-set=xxx
 
    这样所有连接默认的字符集都是xxx,不需要执行SET NAMES了.
 
 
 
字符集的修改步骤
    针对,想同时更改已有数据的字符集的情况.
 
    1).导出表结构
        mysqldump -uroot -p --default-character-set=old_charset -d database_name [ tbl_name ]> xxx.sql
        其中, -d 表示只导出表结构,不导出数据
        如果导出某个数据库中的所有表 , 省略 tbl_name ; 反之,指定要导出数据的表名tbl_name.
 
    2).手动更改xxx.sql中的表结构定义的字符集为新的字符集
 
    3).确保记录不再更新的情况下,导出所有记录
        mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=old_charset  database_name [ tbl_name ] > data.sql
        其中 --quick :                            用于转储大的表.它强制mysqldump从服务器一次一行的检错表中的行,而不是一次性全部检索,并在输出前缓存到内存中.
        --no-create-info:                        不写重新创建每个转储表的CREATE TABLE 语句.
        --default-character-set= old_charset : 按照原有的字符集导出所有数据,不会乱码.
        —extended-insert : 这样插入语句 会是多条insert语句,性能好,mysql默认是这种. 
 
    4).打开data.sql , 将SET NAMES old_charset 修改成 SET NAMES new_charset ;
 
    5).使用新的字符集创建新的数据库
        CREATE DATABASE new_database_name default charset new_charset ;
 
    6).创建表,执行xxx.sql
        mysql -uroot -p new_database_name < xxx.sql ;
 
    7).导入数据 , 执行data.sql
        mysql -uroot -p new_database_name < data.sql ;
 
posted @ 2019-07-04 13:44  豆豆飞  阅读(133)  评论(0编辑  收藏  举报