MySQL字符集与排序规则

  • 创建数据库在开发过程并不是一个陌生的动作，最简单的命令就是：
CREATE DATABASE book_bms;
  • 这样就简简单单的创建了一个数据库，但是明面上并没有指定字符集与排序规则，都是采用了默认的方式进行设置，但是在可视化工具Navicat创建数据库的时候又有这方便的提示，不懂的小伙伴也是一头雾水：

字符集

  • 在MySQL中，字符集（Character Set）是指用于存储数据库中字符数据的一组规则。字符集决定了数据库能够存储哪些字符以及如何存储它们。指定字符集是在创建数据库时设置的一个参数，它影响了数据库中所有表的字符集。
  • MySQL支持多种字符集，这些字符集涵盖了广泛的Unicode字符以及常见的单字节字符集。以下是一些MySQL中常见的字符集：
  • 单字节字符集：
  • latin1：又称ISO-8859-1，适用于西欧语言。
  • utf8：又称UTF-8，是一种变长的编码，可以存储所有Unicode字符。
  • ascii：只包含ASCII字符。
  • 多字节字符集
  • utf8mb4：扩展的UTF-8编码，可以存储包括emoji在内的所有Unicode字符。
  • utf16：使用16位编码来存储Unicode字符。
  • utf32：使用32位编码来存储Unicode字符。
  • 其他字符集：
  • binary：二进制比较字符串，不区分字符的实际值，用于需要精确比较的场景。
  • armscii8、cp1250、cp1251、cp1256等：这些是针对特定区域和语言的编码。
  • 当您创建一个MySQL数据库时，可以指定一个默认的字符集，例如utf8mb4，这样在该数据库中创建的所有表都会使用这个字符集。如果您创建表时没有指定字符集，则表会使用数据库级别的默认字符集。
  • 指定字符集很重要，因为它不仅影响数据的存储，还影响数据的比较和排序。例如，如果您的数据库需要存储多种语言的数据，您可能需要选择一个支持广泛Unicode字符的字符集，如utf8mb4。
  • 在创建数据库时，您可以使用以下SQL语句来指定字符集和排序规则：
CREATE DATABASE book_bms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  • 在这个例子中，utf8mb4是字符集，而utf8mb4_unicode_ci是排序规则，它表示不区分大小写，并使用Unicode标准进行排序。

排序规则

  • 在MySQL中，排序规则（Collation）定义了如何比较数据库中的字符串。每种字符集都有与之相关的多种排序规则，这些规则决定了如何解释字符及其在比较中的排序顺序。例如，对于utf8mb4字符集，有不同的排序规则，如utf8mb4_general_ci、utf8mb4_bin、utf8mb4_unicode_ci等。
    排序规则通常包含以下几个部分：
  • 字符集名称：表示该排序规则适用的字符集，例如utf8mb4。
  • 校对类型（Case Insensitivity）：表示是否区分大小写，ci表示不区分大小写，cs表示区分大小写。
  • 排序规则：表示如何比较字符，例如alphabetic、digit、case等。
  • 特殊排序要求：例如utf8mb4_unicode_520_ci表示使用Unicode 5.2.0版本的排序规则，适用于某些特殊需求。
    以下是一些常见的MySQL排序规则示例：
  • utf8mb4_general_ci：默认排序规则，不区分大小写，适用于大部分场合。
  • utf8mb4_bin：二进制比较，区分大小写，适用于需要精确比较字符串的场景。
  • utf8mb4_unicode_ci：不区分大小写，使用Unicode标准进行排序。
  • utf8mb4_unicode_520_ci：使用特定的Unicode版本进行排序，适用于某些特殊语言或字符。
  • latin1_swedish_ci：对于latin1字符集，不区分大小写，适用于瑞典语或其他使用西欧字符集的语言。
  • 在创建数据库时指定排序规则，可以确保数据库中的数据按照您的预期进行比较和排序。例如，如果您创建了一个数据库，并希望所有数据都不区分大小写，那么您可以使用utf8mb4_unicode_ci作为排序规则。
  • 在MySQL中，您可以在创建表时为表的字段指定排序规则，也可以在创建数据库时指定默认的排序规则。如果您在创建表时没有指定排序规则，那么将使用数据库级别的默认排序规则。

如何修改已创建的字符集与排序规则

  • 要更改现有数据库的字符集和排序规则，您需要使用ALTER DATABASE语句。请注意，这个操作可能会影响数据库中的所有表和数据，因此在执行之前应该备份您的数据库。
  • 以下是一个示例SQL语句，用于将现有数据库old_db的字符集更改为utf8mb4，并更改排序规则为utf8mb4_unicode_ci：
ALTER DATABASE old_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  • 如果您只想更改数据库的排序规则，而不改变字符集，可以使用以下语句：
ALTER DATABASE old_db COLLATE utf8mb4_unicode_ci;
  • 同样，如果您只想更改字符集，而不改变排序规则，可以使用以下语句：
ALTER DATABASE old_db CHARACTER SET utf8mb4;
  • 在执行这些操作之前，请确保：
  • 您有足够的权限来更改数据库。
  • 您已经备份了数据库，以防出现任何问题。
  • 您了解更改字符集和排序规则可能对现有数据和应用程序的影响。
  • 请注意，更改数据库的字符集和排序规则可能会导致数据在表之间或表内的比较行为发生变化。特别是，如果表中有二进制数据（如图片或文件内容），更改字符集可能会导致数据损坏。因此，在执行这样的操作之前，务必进行彻底的测试和评估。