精华内容
下载资源
问答
  • 2022-05-24 20:10:06

    原文网址:MySQL--校对规则(COLLATION)--使用/教程_IT利刃出鞘的博客-CSDN博客

    简介

            本文介绍MySQL的校对规则。

    MySQL的校对规则

    • 两个不同的字符集不能有相同的校对规则。
    • 每个字符集有一个默认校对规则。例如:
      • utf8mb4默认校对规则:utf8mb4_general_ci。
      • utf8默认校对规则:utf8_general_ci。
    • 存在校对规则命名约定:它们以其相关的字符集名开始,通常包括一个语言名,并且以_ci(大小写不敏感)、_cs(大小写敏感)或_bin(二进制,大小写敏感)结束。

    校对规则列举 

    utf8mb4_general_ci
    utf8mb4_unicode_ci
    utf8mb4_general_cs
    utf8mb4_bin

    //不存在utf8_unicode_cs

    查看校对规则

     查看支持的校验规则

    mysql> SHOW COLLATION like 'utf8%';
    +--------------------------+---------+-----+---------+----------+---------+
    | Collation                | Charset | Id  | Default | Compiled | Sortlen |
    +--------------------------+---------+-----+---------+----------+---------+
    | utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |
    | utf8_bin                 | utf8    |  83 |         | Yes      |       1 |
    | utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 |
    ...
    | utf8mb4_general_ci       | utf8mb4 |  45 | Yes     | Yes      |       1 |
    | utf8mb4_bin              | utf8mb4 |  46 |         | Yes      |       1 |
    | utf8mb4_unicode_ci       | utf8mb4 | 224 |         | Yes      |       8 |
    | utf8mb4_icelandic_ci     | utf8mb4 | 225 |         | Yes      |       8 |

    查看本地的校验规则

    mysql> show global variables like '%coll%';
    +----------------------+--------------------+
    | Variable_name        | Value              |
    +----------------------+--------------------+
    | collation_connection | utf8mb4_unicode_ci |
    | collation_database   | utf8mb4_unicode_ci |
    | collation_server     | utf8mb4_unicode_ci |
    +----------------------+--------------------+

    修改校对规则

    修改数据库配置后,不会对已经存在的表造成影响,如要生效需要修改特定列的排序规则。优先级:列>表>数据库>服务器。

    对已存在的表修改

    # 修改数据库:  
    ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;  
    # 修改表:  
    ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;  
    # 修改表字段:  
    ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL ; 

    创建表时指定

    CREATE TABLE `T` (
      `name` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

    utf8_general_ci 与 utf8_unicode_ci区别

    utf8mb4_unicode_ciutf8mb4_general_ci总结
    准确性基于标准的Unicode来排序和比较,能够在各种语言之间精确排序没有实现Unicode排序规则,在遇到某些特殊语言或字符时,排序结果可能不是所期望的。绝大多数情况下,特殊字符的顺序不需要那么精确。
    性能在特殊情况下,Unicode排序规则为了能够处理特殊字符的情况,实现了略微复杂的排序算法。在比较和排序的时候更快

    绝大多数情况下,不会发生此类复杂比较。

    总结:

    utf8_unicode_ci比较准确,utf8_general_ci速度比较快。

    通常情况下 utf8_general_ci 的准确性就够我们用的了,在我看过很多程序源码后,发现它们大多数也用的是utf8_general_ci,所以新建数据 库时一般选用utf8_general_ci就可以了。

    如果是utf8mb4那么对应的就是 utf8mb4_general_ci utf8mb4_unicode_ci

    更多相关内容
  • 在一个团队项目中,没有约定好Collation, 在 MS SQL Server中编程就会遇到这样的问题:Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the ...
  • MySQL collation方法

    2020-09-11 10:42:00
    在以前用oracle的时候,很少关于它的collation方法,但是在mysql中,这点不加注意的话,却有可能会出现问题。
  • 主要介绍了WordPress导入数据库出现”Unknown collation: ‘utf8mb4_unicode_ci”错误的解决办法的相关资料,需要的朋友可以参考下
  • 经常发现同事爲了更改DB的Collation而删掉DB,重新创建过。其实,只要一条SQL就可以解,唔使哽麻烦!!
  • 创建数据库: CREATE DATABASE db_name [[DEFAULT] CHARACTER SET charset_name] [[DEFAULT] COLLATE collation_name] 修改数据库: ALTER DATABASE db_name [[DEFAULT] CHARACTER SET charset_name] [[DEFAULT] ...

    一.内容概述

    在MySQL的使用过程中,了解字符集、字符序的概念,以及不同设置对数据存储、比较的影响非常重要。不少同学在日常工作中遇到的“乱码”问题,很有可能就是因为对字符集与字符序的理解不到位、设置错误造成的。

    本文由浅入深,分别介绍了如下内容:

    字符集、字符序的基本概念及联系

    MySQL支持的字符集、字符序设置级,各设置级别之间的联系

    server、database、table、column级字符集、字符序的查看及设置

    应该何时设置字符集、字符序

    二.字符集、字符序的概念与联系

    在数据的存储上,MySQL提供了不同的字符集支持。而在数据的对比操作上,则提供了不同的字符序支持。

    MySQL提供了不同级别的设置,包括server级、database级、table级、column级,可以提供非常精准的设置。

    什么是字符集、字符序?简单的来说:

    字符集(character set):定义了字符以及字符的编码。

    字符序(collation):定义了字符的比较规则。

    举个例子:

    有四个字符:A、B、a、b,这四个字符的编码分别是A = 0, B = 1, a = 2, b = 3。这里的字符 + 编码就构成了字符集(character set)。

    如果我们想比较两个字符的大小呢?比如A、B,或者a、b,最直观的比较方式是采用它们的编码,比如因为0 < 1,所以 A < B。

    另外,对于A、a,虽然它们编码不同,但我们觉得大小写字符应该是相等的,也就是说 A == a。

    这上面定义了两条比较规则,这些比较规则的集合就是collation。

    同样是大写字符、小写字符,则比较他们的编码大小;

    如果两个字符为大小写关系,则它们相等。

    三、MySQL支持的字符集、字符序

    MySQL支持多种字符集 与 字符序。

    一个字符集对应至少一种字符序(一般是1对多)。

    两个不同的字符集不能有相同的字符序。

    每个字符集都有默认的字符序。

    上面说的比较抽象,我们看下后面几个小节就知道怎么回事了。

    1.查看支持的字符集

    可以通过以下方式查看MYSQL支持的字符集。

    方式一:

    mysql> SHOW CHARACTER SET;

    +----------+-----------------------------+---------------------+--------+

    | Charset | Description | Default collation | Maxlen |

    +----------+-----------------------------+---------------------+--------+

    | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |

    | dec8 | DEC West European | dec8_swedish_ci | 1 |

    ...省略

    方式二:

    mysql> use information_schema;

    mysql> select * from CHARACTER_SETS;

    +--------------------+----------------------+-----------------------------+--------+

    | CHARACTER_SET_name | DEFAULT_COLLATE_name | DESCRIPTION | MAXLEN |

    +--------------------+----------------------+-----------------------------+--------+

    | big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 |

    | dec8 | dec8_swedish_ci | DEC West European | 1 |

    ...省略

    当使用SHOW CHARACTER SET查看时,也可以加上WHERE或LIKE限定条件。

    例子一:使用WHERE限定条件。

    mysql> SHOW CHARACTER SET WHERE Charset="utf8";

    +---------+---------------+-------------------+--------+

    | Charset | Description | Default collation | Maxlen |

    +---------+---------------+-------------------+--------+

    | utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

    +---------+---------------+-------------------+--------+

    1 row in set (0.00 sec)

    例子二:使用LIKE限定条件。

    mysql> SHOW CHARACTER SET LIKE "utf8%";

    +---------+---------------+--------------------+--------+

    | Charset | Description | Default collation | Maxlen |

    +---------+---------------+--------------------+--------+

    | utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

    | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |

    +---------+---------------+--------------------+--------+

    2 rows in set (0.00 sec)

    2.查看支持的字符序

    类似的,可以通过如下方式查看MYSQL支持的字符序。

    方式一:通过SHOW COLLATION进行查看。

    可以看到,utf8字符集有超过10种字符序。通过Default的值是否为Yes,判断是否默认的字符序。

    mysql> SHOW COLLATION WHERE Charset = 'utf8';

    +--------------------------+---------+-----+---------+----------+---------+

    | Collation | Charset | Id | Default | Compiled | Sortlen |

    +--------------------------+---------+-----+---------+----------+---------+

    | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |

    | utf8_bin | utf8 | 83 | | Yes | 1 |

    ...略

    方式二:查询information_schema.COLLATIONS。

    mysql> USE information_schema;

    mysql> SELECT * FROM COLLATIONS WHERE CHARACTER_SET_name="utf8";

    +--------------------------+--------------------+-----+------------+-------------+---------+

    | COLLATION_name | CHARACTER_SET_name | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |

    +--------------------------+--------------------+-----+------------+-------------+---------+

    | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |

    | utf8_bin | utf8 | 83 | | Yes | 1 |

    | utf8_unicode_ci | utf8_name | 192 | | Yes | 8 |

    3.字符序的命名规范

    字符序的命名,以其对应的字符集作为前缀,如下所示。比如字符序utf8_general_ci,标明它是字符集utf8的字符序。更多规则可以参考 官方文档。

    MariaDB [information_schema]> SELECT CHARACTER_SET_name, COLLATION_name FROM COLLATIONS WHERE CHARACTER_SET\_name="utf8" limit 2;

    +--------------------+-----------------+

    | CHARACTER_SET_name | COLLATION_name |

    +--------------------+-----------------+

    | utf8 | utf8_general_ci |

    | utf8 | utf8_bin |

    +--------------------+-----------------+

    2 rows in set (0.00 sec)

    四.server的字符集、字符序

    用途:当你创建数据库,且没有指定字符集、字符序时,server字符集、server字符序就会作为该数据库的默认字符集、排序规则。

    如何指定:MySQL服务启动时,可通过命令行参数指定。也可以通过配置文件的变量指定。

    server默认字符集、字符序:在MySQL编译的时候,通过编译参数指定。

    character_set_server、collation_server分别对应server字符集、server字符序。

    1.查看server字符集、字符序

    分别对应character_set_server、collation_server两个系统变量。

    mysql> SHOW VARIABLES LIKE "character_set_server";

    mysql> SHOW VARIABLES LIKE "collation_server";

    2.启动服务时指定

    可以在MySQL服务启动时,指定server字符集、字符序。如不指定,默认的字符序分别为latin1、latin1_swedish_ci

    mysqld --character-set-server=latin1 \

    --collation-server=latin1_swedish_ci

    单独指定server字符集,此时,server字符序为latin1的默认字符序latin1_swedish_ci。

    mysqld --character-set-server=latin1

    3.配置文件指定

    除了在命令行参数里指定,也可以在配置文件里指定,如下所示。

    [client]

    default-character-set=utf8

    [mysql]

    default-character-set=utf8

    [mysqld]

    collation-server = utf8_unicode_ci

    init-connect='SET NAMES utf8'

    character-set-server = utf8

    4.运行时修改

    例子:运行时修改(重启后会失效,如果想要重启后保持不变,需要写进配置文件里)

    mysql> SET character_set_server = utf8 ;

    5.编译时指定默认字符集、字符序

    character_set_server、collation_server的默认值,可以在MySQL编译时,通过编译选项指定:

    cmake . -DDEFAULT_CHARSET=latin1 \

    -DDEFAULT_COLLATION=latin1_german1_ci

    五.database的字符集、字符序

    用途:指定数据库级别的字符集、字符序。同一个MySQL服务下的数据库,可以分别指定不同的字符集/字符序。

    1.设置数据的字符集/字符序

    可以在创建、修改数据库的时候,通过CHARACTER SET、COLLATE指定数据库的字符集、排序规则。

    创建数据库:

    CREATE DATABASE db_name

    [[DEFAULT] CHARACTER SET charset_name]

    [[DEFAULT] COLLATE collation_name]

    修改数据库:

    ALTER DATABASE db_name

    [[DEFAULT] CHARACTER SET charset_name]

    [[DEFAULT] COLLATE collation_name]

    例子:创建数据库test_schema,字符集设置为utf8,此时默认的排序规则为utf8_general_ci。

    CREATE DATABASE `test_schema` DEFAULT CHARACTER SET utf8;

    2.查看数据库的字符集/字符序

    有3种方式可以查看数据库的字符集/字符序。

    例子一:查看test_schema的字符集、排序规则。(需要切换默认数据库)

    mysql> use test_schema;

    Database changed

    mysql> SELECT @@character_set_database, @@collation_database;

    +--------------------------+----------------------+

    | @@character_set_database | @@collation_database |

    +--------------------------+----------------------+

    | utf8 | utf8_general_ci |

    +--------------------------+----------------------+

    1 row in set (0.00 sec)

    例子二:也可以通过下面命令查看test_schema的字符集、数据库(不需要切换默认数据库)

    mysql> SELECT SCHEMA_name, DEFAULT_CHARACTER_SET_name, DEFAULT_COLLATION_name FROM information_schema.SCHEMATA WHERE schema_name="test_schema";

    +-------------+----------------------------+------------------------+

    | SCHEMA_name | DEFAULT_CHARACTER_SET_name | DEFAULT_COLLATION_name |

    +-------------+----------------------------+------------------------+

    | test_schema | utf8 | utf8_general_ci |

    +-------------+----------------------------+------------------------+

    1 row in set (0.00 sec)

    例子三:也可以通过查看创建数据库的语句,来查看字符集。

    mysql> SHOW CREATE DATABASE test_schema;

    +-------------+----------------------------------------------------------------------+

    | Database | Create Database |

    +-------------+----------------------------------------------------------------------+

    | test_schema | CREATE DATABASE `test_schema` /*!40100 DEFAULT CHARACTER SET utf8 */ |

    +-------------+----------------------------------------------------------------------+

    1 row in set (0.00 sec)

    3.database字符集、字符序是怎么确定的

    创建数据库时,指定了CHARACTER SET或COLLATE,则以对应的字符集、排序规则为准。

    创建数据库时,如果没有指定字符集、排序规则,则以

    character_set_server、collation_server为准。

    六.table的字符集、字符序

    创建表、修改表的语法如下,可通过CHARACTER SET、COLLATE设置字符集、字符序。

    CREATE TABLE tbl_name (column_list)

    [[DEFAULT] CHARACTER SET charset_name]

    [COLLATE collation_name]]

    ALTER TABLE tbl_name

    [[DEFAULT] CHARACTER SET charset_name]

    [COLLATE collation_name]

    1.创建table并指定字符集/字符序

    例子如下,指定字符集为utf8,字符序则采用默认的。

    CREATE TABLE `test_schema`.`test_table` (

    `id` INT NOT NULL COMMENT '',

    PRIMARY KEY (`id`) COMMENT '')

    DEFAULT CHARACTER SET = utf8;

    2.查看table的字符集/字符序

    同样,有3种方式可以查看table的字符集/字符序。

    方式一:通过SHOW TABLE STATUS查看table状态,注意Collation为utf8_general_ci,对应的字符集为utf8。

    MariaDB [blog]> SHOW TABLE STATUS FROM test_schema \G;

    *************************** 1. row ***************************

    Name: test_table

    Engine: InnoDB

    Version: 10

    Row_format: Compact

    Rows: 0

    Avg_row_length: 0

    Data_length: 16384

    Max_data_length: 0

    Index_length: 0

    Data_free: 11534336

    Auto_increment: NULL

    Create_time: 2018-01-09 16:10:42

    Update_time: NULL

    Check_time: NULL

    Collation: utf8_general_ci

    Checksum: NULL

    Create_options:

    Comment:

    1 row in set (0.00 sec)

    方式二:查看information_schema.TABLES的信息。

    mysql> USE test_schema;

    mysql> SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = "test_schema" AND TABLE\_name = "test_table";

    +-----------------+

    | TABLE_COLLATION |

    +-----------------+

    | utf8_general_ci |

    +-----------------+

    方式三:通过SHOW CREATE TABLE确认。

    mysql> SHOW CREATE TABLE test_table;

    +------------+----------------------------------------------------------------------------------------------------------------+

    | Table | Create Table |

    +------------+----------------------------------------------------------------------------------------------------------------+

    | test_table | CREATE TABLE `test_table` (

    `id` int(11) NOT NULL,

    PRIMARY KEY (`id`)

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

    +------------+----------------------------------------------------------------------------------------------------------------+

    1 row in set (0.00 sec)

    3.table字符集、字符序如何确定

    假设CHARACTER SET、COLLATE的值分别是charset_name、collation_name。如果创建table时:

    明确了charset_name、collation_name,则采用charset_name、collation_name。

    只明确了charset_name,但collation_name未明确,则字符集采用charset_name,字符序采用charset_name对应的默认字符序。

    只明确了collation_name,但charset_name未明确,则字符序采用collation_name,字符集采用collation_name关联的字符集。

    charset_name、collation_name均未明确,则采用数据库的字符集、字符序设置。

    七.column的字符集、排序

    类型为CHAR、VARCHAR、TEXT的列,可以指定字符集/字符序,语法如下:

    col_name {CHAR | VARCHAR | TEXT} (col_length)

    [CHARACTER SET charset_name]

    [COLLATE collation_name]

    1.新增column并指定字符集/排序规则

    例子如下:(创建table类似)

    mysql> ALTER TABLE test_table ADD COLUMN char_column VARCHAR(25) CHARACTER SET utf8;

    2.查看column的字符集/字符序

    例子如下:

    mysql> SELECT CHARACTER_SET_name, COLLATION_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA="test_schema" AND TABLE_name="test_table" AND COLUMN_name="char_column";

    +--------------------+-----------------+

    | CHARACTER_SET_name | COLLATION_name |

    +--------------------+-----------------+

    | utf8 | utf8_general_ci |

    +--------------------+-----------------+

    1 row in set (0.00 sec)

    3.column字符集/排序规则确定

    假设CHARACTER SET、COLLATE的值分别是charset_name、collation_name:

    如果charset_name、collation_name均明确,则字符集、字符序以charset_name、collation_name为准。

    只明确了charset_name,collation_name未明确,则字符集为charset_name,字符序为charset_name的默认字符序。

    只明确了collation_name,charset_name未明确,则字符序为collation_name,字符集为collation_name关联的字符集。

    charset_name、collation_name均未明确,则以table的字符集、字符序为准。

    八.选择:何时设置字符集、字符序

    一般来说,可以在三个地方进行配置:

    创建数据库的时候进行配置。

    mysql server启动的时候进行配置。

    从源码编译mysql的时候,通过编译参数进行配置

    1.方式一:创建数据库的时候进行配置

    这种方式比较灵活,也比较保险,它不依赖于默认的字符集/字符序。当你创建数据库的时候指定字符集/字符序,后续创建table、column的时候,如果不特殊指定,会继承对应数据库的字符集/字符序。

    CREATE DATABASE mydb

    DEFAULT CHARACTER SET utf8

    DEFAULT COLLATE utf8_general_ci;

    2.方式二:mysql server启动的时候进行配置

    可以添加以下配置,这样mysql server启动的时候,会对character-set-server、collation-server进行配置。

    当你通过mysql client创建database/table/column,且没有显示声明字符集/字符序,那么就会用character-set-server/collation-server作为默认的字符集/字符序。

    另外,client、server连接时的字符集/字符序,还是需要通过SET NAMES进行设置。

    [mysqld]

    character-set-server=utf8

    collation-server=utf8_general_ci

    3.方式三:从源码编译mysql的时候,通过编译参数进行设置

    编译的时候如果指定了-DDEFAULT_CHARSET和-DDEFAULT_COLLATION,那么:

    创建database、table时,会将其作为默认的字符集/字符序。

    client连接server时,会将其作为默认的字符集/字符序。(不用单独SET NAMES)

    shell> cmake . -DDEFAULT_CHARSET=utf8 \

    -DDEFAULT_COLLATION=utf8_general_ci

    九.写在后面

    本文较为详细地介绍了MySQL中字符集、字符序相关的内容,这部分内容主要针对的是数据的存储与比较。其实还有很重要的一部分内容还没涉及:针对连接的字符集、字符序设置。

    由于连接的字符集、字符序设置不当导致的乱码问题也非常多,这部分内容展开来讲内容也不少,放在下一篇文章进行讲解。

    篇幅所限,有些内容没有细讲,感兴趣的同学欢迎交流,或者查看官方文档。如有错漏,敬请指出。

    参考

    展开全文
  • 字符集和Collations的一般介绍一个character set (字符集)是一组符号和编码,而一个 collation 是在一个字符集里比较字符的一套规则,让我们通过一个虚构的字符集例子来说明区别。假设我们有个四个字母的字母表:`A'...

    字符集和Collations的一般介绍

    一个character set (字符集)是一组符号和编码,而一个 collation 是在一个字符集里

    比较字符的一套规则,让我们通过一个虚构的字符集例子来说明区别。

    假设我们有个四个字母的字母表:`A', `B', `a', `b'.我们给每个字母一个编号:

    `A' = 0, `B' = 1, `a' = 2, `b' = 3. 字母`A' 是一个符号,而数字0是 `A' 的

    encoding(编码),而这四个字母和他们的编码合起来就是一个字符集(character set)。

    现在,假设我们要比较两个字符串的值,`A' 和`B',最简单的方法是看编码,`A' 是 0

    而 `B'是 1. 因为0比1小,我们就说`A' 比 `B' 小。现在,我们就算已经对我们的字符

    集使用了一个collation,collation 是一组规则(在这个例子里只有一条规则):

    "比较编码".我们把所有可能的 collation 中最简单的这种叫做binary collation

    但是如果我们想让大写字母和小写字母一样怎么办?那么我们就得有两条规则:

    (1)把小写字母`a' 和 `b' 看作跟 `A' 和 `B'相等;

    (2)然后比较编码。

    我们称这是一个case-insensitive collation(不区分大小写的 collation).

    这比binary collation 稍微复杂了一点。

    在实际生活中,大多数字符集都包含很多字符:不是仅仅`A'和`B' 而是整个字母表,

    有时是多个字母表或者东方书写系统里几千的字符,和很多专有符号和标点符。

    并且在实际生活中,大多数的collations 有很多规则:除了不区分大小写外还有不区分

    重音(重音``accent'' 是像在德语里字符附加的重音符那样的)和多字符映射。

    MySQL 4.1 可以为你做以下事:

    ·使用各种字符集存储字符串

    ·使用各种collation比较字符串。

    ·在同一台服务器上或者同一个数据库甚至同一个表中使用不同的字符集和collation混合

    ·允许在任何级别上指明字符集和collation

    在这些方面,MySQL 4.1 不只远远比MySQL 4.0复杂,也比其他DBMS先进很多。不过要想

    有效的使用这些新特性,你需要学习哪些字符集和collation是可用的,怎样把他们改成

    默认,还有各种字符串运算符如何操作他们。

    10.2 MySQL 里的字符集和Collations

    MySQL 服务器可支持多个字符集。要列出可用的字符集,使用 SHOW CHARACTER SET 语句:

    mysql> SHOW CHARACTER SET;

    +----------+-----------------------------+---------------------+

    | Charset | Description | Default collation |

    +----------+-----------------------------+---------------------+

    | big5 | Big5 Traditional Chinese | big5_chinese_ci |

    | dec8 | DEC West European | dec8_swedish_ci |

    | cp850 | DOS West European | cp850_general_ci |

    | hp8 | HP West European | hp8_english_ci |

    | koi8r | KOI8-R Relcom Russian | koi8r_general_ci |

    | latin1 | ISO 8859-1 West European | latin1_swedish_ci |

    | latin2 | ISO 8859-2 Central European | latin2_general_ci |

    ...

    输出实际上包含另一列,这里为了让例子在页面上显示更合适,没显示出来

    任一给出的字符集至少包含一个collation. 它可能包含多个 collations.

    要列出一个字符集的 collations , 使用 SHOW COLLATION 语句. 例如, 要看latin1

    (``ISO-8859-1 West European'')的collations, 使用这个语句来找到哪些名字以latin1

    开头的collation

    mysql> SHOW COLLATION LIKE 'latin1%';

    +-------------------+---------+----+---------+----------+---------+

    | Collation | Charset | Id | Default | Compiled | Sortlen |

    +-------------------+---------+----+---------+----------+---------+

    | latin1_german1_ci | latin1 | 5 | | | 0 |

    | latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 |

    | latin1_danish_ci | latin1 | 15 | | | 0 |

    | latin1_german2_ci | latin1 | 31 | | Yes | 2 |

    | latin1_bin | latin1 | 47 | | Yes | 1 |

    | latin1_general_ci | latin1 | 48 | | | 0 |

    | latin1_general_cs | latin1 | 49 | | | 0 |

    | latin1_spanish_ci | latin1 | 94 | | | 0 |

    +-------------------+---------+----+---------+----------+---------+

    latin1 collations 有下列含义:

    Collation 含义

    latin1_bin Binary according to latin1 encoding

    latin1_danish_ci Danish/Norwegian

    latin1_general_ci Multilingual

    latin1_general_cs Multilingual, case sensitive

    latin1_german1_ci German DIN-1

    latin1_german2_ci German DIN-2

    latin1_spanish_ci Modern Spanish

    latin1_swedish_ci Swedish/Finnish

    Collations 有这些一般特性:

    ·两个不同字符集没法拥有同一个collation.

    ·每个字符集有一个默认 collation. 例如, latin1 的默认 collation 是

    latin1_swedish_ci.

    ·collation 的命名有个约定: 他们由所关联的字符集的名字打头,他们通常包含一个

    语言名, 并以 _ci (case insensitive大小写不敏感),

    或者 _cs (case sensitive大小写敏感), 或者 _bin (binary二进制).

    10.3 决定默认字符集和 Collation

    有四个级别上的默认字符集和collation设置: 服务器,数据库,表和连接。下面的描述

    可能看起来复杂,不过实践中得出多级默认设置可以带来自然而然的结果。

    10.3.1 服务器级字符集和 Collation

    MySQL服务器有一个服务器级别的字符集和 collation, 不能为空。

    MySQL 这样决定服务器级的字符集和collation

    ·当服务器开始按照有效选项设置

    ·运行期间按照变量

    在服务器级别,决定是很简单的,依靠你执行mysqld时使用的选项来决定服务器字符集

    和collation。你可以使用--default-character-set 来指定字符集,并且和这个一起

    还可以为collation加上--default-collation 。如果你不指定字符集,就相当于说

    --default-character-set=latin1。如果你只指定了字符集(例如,latin1)但是没有指定

    collation,就相当于

    --default-charset=latin1 --default-collation=latin1_swedish_ci

    因为 latin1_swedish_ci是latin1字符集的默认collation, 因此下面三个命令都具有

    同样效果:

    shell> mysqld

    shell> mysqld --default-character-set=latin1

    shell> mysqld --default-character-set=latin1 /

    --default-collation=latin1_swedish_ci

    有个改变这个设置的方法是重新编译,如果你想编译源码来改变默认的服务器字符集和

    collation,在configure使加上参数--with-charset 和 --with-collation ,例如:

    shell> ./configure --with-charset=latin1

    或者:

    shell> ./configure --with-charset=latin1 /

    mysqld 和configure 都会核实字符集/collation的结合是否有效,如果无效,这两个

    程序都会报错并中止。

    现行服务器字符集和collation 是和character_set_server 和 collation_server

    这两个系统变量的值一样,这些变量可以在运行时更改

    10.3.2 数据库字符集和 Collation

    每个数据库都有一个数据库字符集和数据库collation,并且不能为空,CREATE DATABASE

    和 ALTER DATABASE 语句有专门指明数据库字符集和collation的可选子句:

    CREATE DATABASE db_name

    [[DEFAULT] CHARACTER SET charset_name]

    [[DEFAULT] COLLATE collation_name]

    ALTER DATABASE db_name

    [[DEFAULT] CHARACTER SET charset_name]

    [[DEFAULT] COLLATE collation_name]

    例子:

    CREATE DATABASE db_name

    DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

    MySQL可以这样选择数据库字符集和数据库collation:

    ·如果 CHARACTER SET X 和 COLLATE Y 被指定了, 那么字符集是 X collation 是 Y.

    ·如果 CHARACTER SET X 被指定,但是没有指定 COLLATE, 那么字符集是 X collation

    是默认collation.

    ·否则, 就用服务器字符集和服务器 collation.

    MySQL 的 CREATE DATABASE ... DEFAULT CHARACTER SET ... 语法类似于标准 SQL

    的 CREATE SCHEMA ... CHARACTER SET ... 语法. 因为这样, 就可能在同一个MySQL

    服务器上创建具有不同字符集和collation的数据库。

    如果在建表的语句里没有指定表的字符集和collation,那么数据库字符集和 collation

    就作为表的字符集和collation的默认值. 它们没有别的作用。

    默认数据库的字符集和 collation是和 character_set_database 以及

    collation_database 这两个系统变量的值一样。 当默认数据库更改时服务程序会设置

    这些变量的值。如果没有默认数据库, 变量的值会和配套的服务器级系统变量

    character_set_server 以及 collation_server的值一致.

    10.3.3 表字符集和 Collation

    每个表有一个表字符集以及一个表collation,不能为空。CREATE TABLE 和 ALTER TABLE

    语句有可选子句指定表字符集和collation。

    CREATE TABLE tbl_name (column_list)

    [DEFAULT CHARACTER SET charset_name [COLLATE collation_name]]

    ALTER TABLE tbl_name

    [DEFAULT CHARACTER SET charset_name] [COLLATE collation_name]

    例子:

    CREATE TABLE t1 ( ... )

    DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;

    MySQL 通过下面的方法选择表字符集和collation:

    ·如果 CHARACTER SET X 和 COLLATE Y 都被指定了, 那么字符集就是 X collation 是Y

    ·如果只指定了CHARACTER SET X 而没有指定 COLLATE, 那么字符集为 X 并配默认的

    collation.

    ·否则就使用数据库字符集和 collation.

    表字符集和 collation 用来在没有指定个别列字符集和列collation的时候做为它们

    的默认值。表字符集和 collation 是MySQL 的扩展;在标准SQL里没有这种东西

    10.3.4 列字符集和 Collation

    每个``character'' 列(是指列属性为CHAR, VARCHAR, 或 TEXT的)都有一个列字符集

    和一个列collation,不能为空。列定义语句有可选子句指定列字符集和collation:

    col_name {CHAR | VARCHAR | TEXT} (col_length)

    [CHARACTER SET charset_name [COLLATE collation_name]]

    Example:

    CREATE TABLE Table1

    (

    column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci

    );

    MySQL 这样选择列字符集和collation:

    ·如果 CHARACTER SET X 和 COLLATE Y 都被指定了, 那么字符集就是 X collation 就是 Y.

    ·如果指定了 CHARACTER SET X 但没有指定 COLLATE, 那么字符集是 X 并配默认的collation.

    ·否则,就用表字符集和 collation.

    CHARACTER SET 和 COLLATE 子句是标准SQL.

    10.3.5 字符集和 Collation 分配的例子

    下面的例子显示了 MySQL 怎样决定默认的字符集和collation的值:

    例子1:表+列定义

    CREATE TABLE t1

    (

    c1 CHAR(10) CHARACTER SET latin1 COLLATE latin1_german1_ci

    ) DEFAULT CHARACTER SET latin2 COLLATE latin2_bin;

    这里我们有一个用latin1的字符集和latin1_german1_ci collation的列。

    定义非常明显,所以很简单。注意把一个latin1 的列存到一个latin2的表里不会有问题

    例子2:表+列定义

    CREATE TABLE t1

    (

    c1 CHAR(10) CHARACTER SET latin1

    ) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;

    这次我们有一列是latin1字符集加默认的collation。现在,虽然它看上去很自然,

    但是默认的collation却不是从表级继承而来。事实上,因为latin1的默认collation

    始终是latin1_swedish_ci,所以c1列的collation是latin1_swedish_ci (而不是

    latin1_danish_ci).

    例子3:表+列定义

    CREATE TABLE t1

    (

    c1 CHAR(10)

    ) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;

    我们有一个默认字符集和默认collation的列。在这个环境下,MySQL向上到表级决定

    列字符集和collation。所以,c1的列字符集是latin1,它的collation是

    latin1_danish_ci

    例子4:数据库+表+列定义

    CREATE DATABASE d1

    DEFAULT CHARACTER SET latin2 COLLATE latin2_czech_ci;

    USE d1;

    CREATE TABLE t1

    (

    c1 CHAR(10)

    );

    我们创建了一个没有指定列字符集和collation的列。我们也没有指定表级字符集和

    collation。在这个条件下,MySQL向上到数据库级决定。(数据库的设置变为表的设置,

    之后成为列的设置),所以c1的列字符集是latin2,collation是latin2_czech_ci

    10.3.6 连接的字符集和Collations

    一些字符集和collation和用户对服务器的作用结合。有些在前面已经提及了:

    ·服务器的字符集和collation和 character_set_server 及 collation_server 变量

    的值一样

    ·默认数据库的字符集和collation和 character_set_database 及 collation_database

    变量的值一样.

    附加的字符集和collation 变量被引入用来处理服务器和客户端之间连接得通信。每个

    客户端都有连接相关的字符集和collation变量。

    想想“连接”是什么:是你连到服务器时作的事情。客户端通过这条连接发送SQL语句,

    比如查询,服务器则通过这条连接给客户端送回回应,比如查询结果结果集合,这导致

    了客户端处理字符集和collation的一些问题,它们每个都可以按照系统变量来回答:

    ·当查询离开客户端的时候应该是什么字符集的?服务器用character_set_client

    这个变量来作为客户端发送查询所用的字符集

    ·服务器端在接收到了查询以后应该把它翻译到社么字符集里?对于这个,服务程序

    用的是character_set_connection 和 collation_connection 这两个变量。

    它把客户端送来的查询从character_set_client 转换成character_set_connection

    (除了latin1或者utf8 的字符串)。collation_connection 对于比较字符串非常

    重要,对于列值比较字符串是没有关系的,因为列拥有高优先级

    ·当服务程序要送回结果集合或者错误信息给客户端时应该用什么字符集?

    character_set_results 变量指示了这个值,这包括了列值,或者列名等结果数据。

    你可以调整这些变量的值,或者就使用默认的(那样就可以省略这节了)

    有两个语句影响连接字符集设置:

    SET NAMES 'charset_name'

    SET CHARACTER SET charset_name

    SET NAMES 指出客户端送出的SQL语句里是什么。因此,SET NAMES 'cp1251' 就告诉服务

    程序“下面将要从这个客户端送来的信息将是使用'cp1251'这个字符集。这也指定了

    服务程序送回的结果所用的字符集,(例如如果你用了一个SELECT语句它会指出列值

    拥有的字符集)

    SET NAMES 'x' 语句相当于下面三个语句:

    mysql> SET character_set_client = x;

    mysql> SET character_set_results = x;

    mysql> SET character_set_connection = x;

    把character_set_connection 设置成x也会把collation_connection 设置成默认

    collation x

    SET CHARACTER SET 是类似的,不过是把连接字符集和collation设置成那些默认数据库。

    SET CHARACTER SET x 语句相当于这三个语句:

    mysql> SET character_set_client = x;

    mysql> SET character_set_results = x;

    mysql> SET collation_connection = @@collation_database;

    当一个客户连接,它向服务程序发送它想使用的字符集的名字,服务程序把

    character_set_client, character_set_results, 和 character_set_connection

    这些变量设置成那个字符集(事实上,服务程序使用字符集执行了SET NAMES 操作)

    如果你不想用默认字符集,使用 Mysql 客户端程序不需要每次启动时执行SET NAMES 。

    你可以在mysql 执行语句行加上--default-character-set 这个选项,或者在你的选项

    文件里加上。比如,下面的选项文件设置使你每次执行mysql程序时把默认字符集变量

    改成 koi8r:

    [mysql]

    default-character-set=koi8r

    例如:假设column1定义是 CHAR(5) CHARACTER SET latin2。如果你不用SET NAMES

    或者 SET CHARACTER SET,那么对于你的 SELECT column1 FROM t 请求,服务程序

    会把column1 的所有值用连接建立时客户端指定的字符集来回送。另一方面,如果你

    用了 SET NAMES 'latin1' or SET CHARACTER SET latin1 ,那么在送回结果之前,

    服务程序会把 latin2 的值转成latin1,如果里面有两种字符集里都没有的字符,

    转化会有损耗。

    如果你不希望服务程序作任何转换,就把character_set_results 设置成 NULL

    mysql> SET character_set_results = NULL;

    10.3.7. 字符串文字字符集和collation

    每个字符串文字都有自己的字符集和collation,不能为空

    一个字符串文字可能有一个可选字符集introducer和COLLATION子句:

    [_charset_name]'string' [COLLATE collation_name]

    例如:

    SELECT 'string';

    SELECT _latin1'string';

    SELECT _latin1'string' COLLATE latin1_danish_ci;

    对于简单语句 SELECT 'string',字符串的字符集和collation是由两个系统变量

    character_set_connection 和 collation_connection 定义的。

    _charset_name 表达式正式情况下被叫做 introducer .它告诉分析器“下面的字符串

    是使用 X 字符集的。”因为这在以前造成很多人的困扰,我们强调一下introducer

    并不作任何转换,严格来讲并不改变字符串的值,只是一个符号。introducer 在

    标准16进制文字前和数字16进制记法前都是合法的(x'literal' 和 0xnnnn),

    在?前面也是合法的(当在程序设计语言接口里使用预备语句时作参数替换)

    例如:

    SELECT _latin1 x'AABBCC';

    SELECT _latin1 0xAABBCC;

    SELECT _latin1 ?;

    MySQL 这样决定一个文字的字符集和collation:

    ·如果 _X 和 COLLATE Y 都被指定了,那么字符集就是 X collation 是 Y

    ·如果 指定了 _X 而没有指定 COLLATE ,那么字符集是 X collation 是 X 的默认

    collation

    ·否则,由系统变量 character_set_connection 和 collation_connection 决定字符集

    和collation

    例如:

    ·一个字符集是 latin1 而collation是 latin1_german1_ci 的字符串:

    SELECT _latin1'Müller' COLLATE latin1_german1_ci;

    ·一个字符集是 latin1 以及其配套默认collation的(latin1_swedish_ci)字符串:

    SELECT _latin1'Müller';

    ·一个连接默认字符集和collation的字符串:

    SELECT 'Müller';

    字符集 introducer 和 COLLATE 子句是符合标准 SQL 规则的工具

    10.3.8. 在 SQL 语句里使用 COLLATE

    通过 COLLATE 子句,你可以在比较时覆盖替换掉任何默认collation, COLLATE 可以用

    在SQL 语句的很多部分里,这里是一些例子:

    ·在 ORDER BY 里:

    SELECT k

    FROM t1

    ORDER BY k COLLATE latin1_german2_ci;

    ·在 AS 里:

    SELECT k COLLATE latin1_german2_ci AS k1

    FROM t1

    ORDER BY k1;

    ·在GROUP BY里 :

    SELECT k

    FROM t1

    GROUP BY k COLLATE latin1_german2_ci;

    ·在集合函数里:

    SELECT MAX(k COLLATE latin1_german2_ci)

    FROM t1;

    ·在DISTINCT里

    SELECT DISTINCT k COLLATE latin1_german2_ci

    FROM t1;

    ·在WHERE 里:

    SELECT *

    FROM t1

    WHERE _latin1 'Müller' COLLATE latin1_german2_ci = k;

    ·在HAVING里:

    SELECT k

    FROM t1

    GROUP BY k

    HAVING k = _latin1 'Müller' COLLATE latin1_german2_ci;

    User Comments

    Posted by [name withheld] on January 14 2005 2:33pm

    在不同的列/表里:

    SELECT t1.k FROM t1 WHERE NOT EXISTS

    ( SELECT * FROM t2 WHERE t1.k=t2.k COLLATE latin1_german2_ci);

    在collation 之间比较列的时候能够避免出错信息。

    10.3.9. COLLATE 子句优先级

    COLLATE子句具有高优先级(比||高),所以下面两个表达式是相同的:

    x || y COLLATE z

    x || (y COLLATE z)

    10.3.10. BINARY 运算

    BINARY 运算是COLLATE 子句的速记法,BINARY 'x' 和 'x' COLLATE y 是相同的,

    y 是字符集 'x' 的二元collation 的名字。每个字符集都有二元 collation。例如,

    latin1 字符集的 collation 是latin1_bin,所以如果列 a 是latin1 字符集,下面

    两个语句有同样效果:

    SELECT * FROM t1 ORDER BY BINARY a;

    SELECT * FROM t1 ORDER BY a COLLATE latin1_bin;

    10.3.11. 一些决定collation 比较棘手的情况

    在绝大多数查询里,MySQL 用什么collation来进行比较操作都是很显而易见的,例如,

    在下面的情况里,很显然collation 应该是“列 x 的列 collation”:

    SELECT x FROM T ORDER BY x;

    SELECT x FROM T WHERE x = x;

    SELECT DISTINCT x FROM T;

    但是,当卷入了多操作数时,就很难搞了,例如:

    SELECT x FROM T WHERE x = 'Y';

    这个查询应该使用列 x 的collation 呢,还是使用字符串'Y' 的?

    标准SQL 使用被叫做``coercibility'' 的规则来解决这个问题。本质就是:因为

    x 和 'Y' 都有collation ,优先使用谁的collation呢?这很复杂,不过下面的规则能

    应付大多数情况:

    ·一个COLLATE 子句的 coercibility 是0 (也就是根本不coercible)

    ·两个具有不同collation 的字符串连结的 coercibility 是1

    ·一个列的 collation 的 coercibility 是 2

    ·一个文字型的collation 的 coercibility 是3。

    那些规则这样解决含混:

    ·使用具有最低 coercibility 值的collation

    ·如果两边具有相同的 coercibility, 如果两个collation 不同那就是错误。

    例如:

    column1 = 'A' 使用column1的collation

    column1 = 'A' COLLATE x 使用 'A' 的collation

    column1 COLLATE x = 'A' COLLATE y Error

    COERCIBILITY() 函数可以用来判断一个字符串表达式的coercibility:

    mysql> SELECT COERCIBILITY('A' COLLATE latin1_swedish_ci);

    -> 0

    mysql> SELECT COERCIBILITY('A');

    -> 3

    User Comments

    Posted by Thierry Danard on November 5 2004 10:34pm

    对于数据库引擎来说显而易见的排序并不是总那么显而易见(version 4.1).

    一个没有带类似于"select concat(mycolumn, '%') from mytable "这样的排序指令

    的查询在"mycolumn" 和"%"的字符集不相同的情况下不会工作。

    在我这里,整个数据库使用 UTF-8, 默认情况下, '%' 假设是 latin1,

    causing an error to be triggered。

    10.3.12. Collations Must Be for the Right Character Set

    记得说过每个字符集都有一个或者多个collation,每个collation只和一个字符集关联。

    因此,下面的语句会导致错误,因为 latin2_bin 这个collation 和 latin1 这个字符集

    不配套。

    mysql> SELECT _latin1 'x' COLLATE latin2_bin;

    ERROR 1251: COLLATION 'latin2_bin' is not valid

    for CHARACTER SET 'latin1'

    在某些情况下,在 MySQL 4.1 前工作的表达式会在MySQL 4.1以后的版本失败,

    如果你在帐号里没有字符集和collation的话。例如,在 MySQL 4.1 前,这个语句

    会这样工作:

    mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);

    +-------------------------------+

    | SUBSTRING_INDEX(USER(),'@',1) |

    +-------------------------------+

    | root |

    +-------------------------------+

    升级到MySQL 4.1 以后,语句失效:

    mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);

    ERROR 1267 (HY000): Illegal mix of collations

    (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE)

    for operation 'substr_index'

    发生这个的原因是username 使用utf8存储(参看10.6节),因此, USER() 函数

    和文字型字符串'@'具有不同的字符集(当然也是不同collation):

    mysql> SELECT COLLATION(USER()), COLLATION('@');

    +-------------------+-------------------+

    | COLLATION(USER()) | COLLATION('@') |

    +-------------------+-------------------+

    | utf8_general_ci | latin1_swedish_ci |

    +-------------------+-------------------+

    解决的一个方法是告诉MySQL把文字型字符串翻译成utf8:

    mysql> SELECT SUBSTRING_INDEX(USER(),_utf8'@',1);

    +------------------------------------+

    | SUBSTRING_INDEX(USER(),_utf8'@',1) |

    +------------------------------------+

    | root |

    +------------------------------------+

    另一个方法是把连接的字符集和collation改成utf8,你可以使用SET NAMES 'utf8'

    或者直接设置两个系统变量character_set_connection 和 collation_connection

    的值来达到这个目的。

    10.3.13. Collation 的效果的一个例子

    假设表 T 里的列 X 具有这些 latin1 的列值:

    Muffler

    Müller

    MX Systems

    MySQL

    并且假设这些列值可以用下列语句找回:

    SELECT X FROM T ORDER BY X COLLATE collation_name;

    在这张表中列出了不同collation 的结果值的结果排序

    latin1_swedish_ci latin1_german1_ci latin1_german2_ci

    Muffler Muffler Müller

    MX Systems Müller Muffler

    Müller MX Systems MX Systems

    MySQL MySQL MySQL

    这张表显示了如果我们在一个 ORDER BY 子句里使用不同collation 会有什么样的效果

    的例子,导致这种不同排序结果的字符是上面有两个点的 U,在德语里叫做U-曲音,

    不过我们叫做U-分音符

    ·第一列显示了使用瑞典/芬兰 collation 规则的 SELECT 的结果,U-分音符

    通过Y归类

    ·第二列显示了使用德语DIN-1 规则的SELECT 语句的结果,U-分音符通过U归类

    ·第三列显示了使用德语DIN-2 规则的SELECT 语句的结果,U-分音符通过UE归类

    三种不同的collation ,三种不同的结果,这是MySQL 在这里的处理。通过使用合适的

    collation,你可以选择你想要的排序次序。

    该文章转载自宋氏电脑 技术无忧:http://www.pc51.org/data/MySQL/2007-01-04/2600.html

    展开全文
  • 作者:VEPHP 时间 2017-09-15《MYSQL数据库MySQL collation方法》要点:本文介绍了MYSQL数据库MySQL collation方法,希望对您有用。如果有疑问,可以联系我们。问题是这样的:一张test的表,字符集采用的latin1....

    作者:VEPHP   时间 2017-09-15

    《MYSQL数据库MySQL collation方法》要点:

    本文介绍了MYSQL数据库MySQL collation方法,希望对您有用。如果有疑问,可以联系我们。问题是这样的:

    一张test的表,字符集采用的latin1.

    select to_id from test where to_id='cn象_王';

    +---------------+

    | to_id |

    +---------------+

    | cn陶_陶 |

    | cn象_王 |

    +---------------+

    2 rows in set (0.00 sec)

    取cn象_王的数据,居然把cn陶_陶的数据也取回来了.

    这显然是不允许的.

    查看它们的编码:

    (root@im_offlog1a)[test]> select hex('cn陶_陶');

    +----------------+

    | hex('cn陶_陶') |

    +----------------+

    | 636ECCD55FCCD5 |

    +----------------+

    1 row in set (0.00 sec)

    (root@im_offlog1a)[test]> select hex('cn象_王');

    +----------------+

    | hex('cn象_王') |

    +----------------+

    | 636ECFF35FCDF5 |

    +----------------+

    1 row in set (0.00 sec)

    编码的确是不一样的,但是为什么mysql会认为这两条记录是一样的呢?

    一开始我们就把问题定位于collation引起的问题.

    show variables查看

    | collation_connection | latin1_swedish_ci

    | collation_database | latin1_swedish_ci

    | collation_server | latin1_swedish_ci

    手工把这些参数修改为latin1_bin,结果居然一样.这下感觉真是奇怪了.

    这里先解释一下mysql collation的命名规则:

    它们以其相关的字符集名开始,通常包括一个语言名,并且以_ci(大小写不敏感)、_cs(大小写敏感)或_bin(二元)结束

    比如latin1字符集有以下几种校正规则:

    校对规则 含义

    latin1_german1_ci 德国DIN-1

    latin1_swedish_ci 瑞典/芬兰

    latin1_danish_ci 丹麦/挪威

    latin1_german2_ci 德国 DIN-2

    latin1_bin 符合latin1编码的二进制

    latin1_general_ci 多种语言(西欧)

    latin1_general_cs 多种语言(西欧ISO),大小写敏感

    latin1_spanish_ci 现代西班牙

    最后我们将表格重建,手工指定表格级别的collation为latin1_bin.

    这个问题就得到了解决.

    那么问题又来了,为什么我前面手工测试latin1_bin时不生效呢?

    原来MySQL按照下面的方式选择表字符集和 校对规则:

    如果指定了CHARACTER SET X和COLLATE Y,那么采用CHARACTER SET X和COLLATE Y.

    如果指定了CHARACTER SET X而没有指定COLLATE Y,那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则.

    否则,采用服务器字符集和服务器校对规则.

    而我们在建表的时候指定了character set,所以它永远是采用对应的默认的校对规则.

    当然我们其实也没必要重建表格,只需要alter table db_allot CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin这样转换即可.

    另外建议collation都尽量采用字符集相应的bin类型的校对规则,这样不容易出错.

    再说说我自己的体会

    觉得 character set latin1 collate latin1_bin 就是老版的 VARCHAR BINARY 的改进,只是新版的先用 character set 定字符集,再用此字符集名字加 _bin 定校对规则为二进制的,从而确保中文查询正确.

    再测试了一下,把此字段属性改为不带 BINARY 的

    ALTER TABLE `comment_content_1_01` CHANGE `thread` `thread` VARCHAR( 50 ) DEFAULT NULL

    然后再看表结构确实变成 `thread` varchar(50) default NULL, 即不带 character set latin1 collate latin1_bin 了,可见character set latin1 collate latin1_bin 就是老版的 VARCHAR BINARY 的改进.

    此外还读到更方便的做法,不用逐个改字段属性,而只要表格级别的collation为latin1_bin就行了.

    测试:

    alter table comment_content_1_01 CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin

    后,

    再导出表结构

    CREATE TABLE comment_content_1_01 (

    content_id int(11) NOT NULL auto_increment,

    thread varchar(50) collate latin1_bin default NULL,

    uname varchar(100) collate latin1_bin default NULL,

    nick varchar(100) collate latin1_bin default NULL,

    uid int(11) unsigned default NULL,

    content text collate latin1_bin,

    post_time datetime default NULL,

    post_ip int(10) unsigned default NULL,

    `status` enum('unaudit','normal','deleted') collate latin1_bin NOT NULL default 'unaudit',

    PRIMARY KEY (content_id)

    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

    即便原来没定各字段的 collate,现在也全都是 collate latin1_bin 了.

    标签:

    展开全文
  • MySQL字符集和校对规则(Collation

    千次阅读 2019-09-24 17:52:17
    MySQL字符集和校对规则(Collation) 阅读目录:MySQL的字符集和校对规则 MySQL的字符集 ...
  • 使用 mysql 创建数据表的时候, 总免不了要涉及到 character set 和 collation 的概念, 之前不是很了解。这两天不是很忙, 就自己整理了一下。先来看看 character set 和 collation 的是什么?&. character set...
  • MySQL的show collation命令

    2021-01-19 20:02:00
    show collation显示MySQL支持字符集的排序规则。如下:mysql>showcollation;+--------------------------+----------+-----+---------+----------+---------+|Collation|Charset|Id|Default|Compiled...
  • 1 查看当前设置Character: 字符集Collation:在同一字符集内字符之间的比较规则、排序collation查看:SHOW VARIABLES LIKE 'collation_%';+----------------------+--------------------+| Variable_name | Value |+...
  • 这里需要补充一些mysql字符集、collation的知识。mysql中对于字符串会按存储类型进行分类,分为二进制、非二进制。通常二进制存储的都是纯二进制文本,例如mp3、图像文件等,通常设定类型为blob、binary、varbinary...
  • mysql的collation-字符集

    2021-01-19 11:48:18
    set=utf8 default-collation=utf8_general_ci ---------------------------------------------------------------------------------------------------------------- mysql的collation大致的意思就是字符序。...
  • 通过网上查找一些资料,有人建议查看所关联的字段的数据格式是不是一致,通过排查发现了数据库的两个表的character 以及collation设置的不同。ALTER TABLE table MODIFY colunm varchar(50) CHARACTER SET utf8mb4 ...
  • MySql:charset和collation的设置

    千次阅读 2021-03-15 23:55:50
    MySql:charset和collation的设置charset 和 collation 有多个级别的设置:服务器级、数据库级、表级、列级和链接级www.2cto.com1.服务器级查看设置:... 和 show global variables like 'collation_server';修改设置...
  • 末尾附原文链接mysql的collation大致的意思就是字符序。首先字符本来是不分大小的,那么对字符的>, = , < 操作就需要有个字符序的规则。collation做的就是这个事情,你可以对表进行字符序的设置,也...
  • MySql:charset和collation的设置_MySQL

    千次阅读 2021-01-27 12:51:09
    bitsCN.comMySql:charset和collation的设置charset 和 collation 有多个级别的设置:服务器级、数据库级、表级、列级和连接级1.服务器级查看设置:show global variables like 'character_set_server'; 和 show ...
  • MySQL的坑之collation

    2021-03-29 16:03:20
    MySQL的坑真的太多了,以前用PG多,...两个不一致collation的字段,不能进行比较!! 我辛苦百度了半天修修改改的一个合理的规范化collation的脚本如下: delimiter // drop procedure if exists updateCollation// cr
  • mysql数据库collation作用

    千次阅读 2019-09-05 18:58:42
    mysql的collation大致的意思就是字符序。首先字符本来是不分大小的,那么对字符的>, = , < 操作就需要有个字符序的规则。collation做的就是这个事情,你可以对表进行字符序的设置,也可以单独对某个字段进行...
  • [mysql]修改collation为utf8_bin

    千次阅读 2021-01-19 01:35:28
    mysql默认字段值区分大小写:character-set-server=utf8collation-server=utf8_bininit-connect='SET NAMES utf8;'SELECT DEFAULT_CHARACTER_SET_NAME 'charset', DEFAULT_COLLATION_NAME 'collation'FROM ...
  • 23.2. Collation Support

    2021-03-01 22:46:23
    CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de@collation=phonebook'); 23.2.2.3.3 拷贝排序规则 命令CREATE COLLATION也可用于基于现存排序规则创建新的排序规则。 CREATE COLLATION ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 80,730
精华内容 32,292
关键字:

collation