❌

阅读视图

↗️

MySQL字符集及底层原理

✇Liu Zijian's Blog

1.字符集优先级

查看MySQL中的字符集：

mysql> show variables like '%char%';+--------------------------+----------------------------+| Variable_name            | Value                      |+--------------------------+----------------------------+| character_set_client     | utf8                       || character_set_connection | utf8                       || character_set_database   | latin1                     || character_set_filesystem | binary                     || character_set_results    | utf8                       || character_set_server     | latin1                     || character_set_system     | utf8                       || character_sets_dir       | /usr/share/mysql/charsets/ |+--------------------------+----------------------------+8 rows in set (0.00 sec)

  • character_set_server: 服务器级别的字符集

  • character_set_database: 当前数据库的字符集

  • character_set_client: 服务器解码请求时使用的字符集

  • character_set_connection: 服务器处理请求时会把请求字符串从character_set_client转为character_set_connection

  • character_set_results: 服务器向客户端返回数据时使用的字符集

MySQL有4个级别的字符集和比较规则，分别是

  1. 服务器级别: character_set_server，配置在my.cnf配置文件character-set-server的字符集
  2. 数据库级别: character_set_database，character_set_server决定character_set_database
  3. 表级别: 在某个库下建表，如果没有指定表的字符集，默认与当前库的字符集一致
  4. 列级别: 表中的字段不指定字符集的时候，默认采用所在表的字符集。

2.utf8mb3和utf8mb4

查看MySQL支持的字符集（Charset）和对应的比较规则（Default collation）以及占用大小（Maxlen）:

mysql> show charset;+----------+---------------------------------+---------------------+--------+| Charset  | Description                     | Default collation   | Maxlen |+----------+---------------------------------+---------------------+--------+| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 || dec8     | DEC West European               | dec8_swedish_ci     |      1 || cp850    | DOS West European               | cp850_general_ci    |      1 || hp8      | HP West European                | hp8_english_ci      |      1 || koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 || latin1   | cp1252 West European            | latin1_swedish_ci   |      1 || latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 || swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 || ascii    | US ASCII                        | ascii_general_ci    |      1 || ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 || sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 || hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 || tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 || euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 || koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 || gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 || greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 || cp1250   | Windows Central European        | cp1250_general_ci   |      1 || gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 || latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 || armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 || utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 || ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 || cp866    | DOS Russian                     | cp866_general_ci    |      1 || keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 || macce    | Mac Central European            | macce_general_ci    |      1 || macroman | Mac West European               | macroman_general_ci |      1 || cp852    | DOS Central European            | cp852_general_ci    |      1 || latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 || utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 || cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 || utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 || utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 || cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 || cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 || utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 || binary   | Binary pseudo charset           | binary              |      1 || geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 || cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 || eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 || gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |+----------+---------------------------------+---------------------+--------+41 rows in set (0.00 sec)

utf8字符集表示一个字符需要使用1-4个字节，但是我们常用的一些字符使用1-3个字节就可以表示了。而字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能，所以设计MySOL的设计者自定义了两个概念

  • utf8mb3 阉割过的utf8字符集，只使用1-3个字节表示字符。
  • utf8mb4 正宗的utf8字符集，使用1-4个字节表示字符。

在MySQL中utf8是utf8mb3的别名，所以之后在MySQL中提到utf8就意味着使用1-3个字节来表示一个字符。如果有使用4字节编码一个字符的情况，比如存储一些emoii表情，那请使用utf8mb4。

3.比较规则

show charset;表中，一共支持41种字符集，其中的Default collation列表示这种字符集默认的比较规则，里面包含着该比较规则主要作用于哪种语言，比如utf8_polish_ci表示以波兰语的规则比较，utf8_spanish_ci是以西班牙语的规则比较，utf8_general_ci是一种通用的比较规则。

后缀表示该比较规则是否区分语言中的重音、大小写，具体如下:

后缀英文释义描述
_aiaccent insensitive不区分重音
_asaccent sensitive区分重音
_cicase insensitive不区分大小写
_cscase sensitive区分大小写
_binbinary以二进制方式比较

查看某种字符集的比较规则

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 || utf8_icelandic_ci        | utf8    | 193 |         | Yes      |       8 || utf8_latvian_ci          | utf8    | 194 |         | Yes      |       8 || utf8_romanian_ci         | utf8    | 195 |         | Yes      |       8 || utf8_slovenian_ci        | utf8    | 196 |         | Yes      |       8 || utf8_polish_ci           | utf8    | 197 |         | Yes      |       8 || utf8_estonian_ci         | utf8    | 198 |         | Yes      |       8 || utf8_spanish_ci          | utf8    | 199 |         | Yes      |       8 || utf8_swedish_ci          | utf8    | 200 |         | Yes      |       8 || utf8_turkish_ci          | utf8    | 201 |         | Yes      |       8 || utf8_czech_ci            | utf8    | 202 |         | Yes      |       8 || utf8_danish_ci           | utf8    | 203 |         | Yes      |       8 || utf8_lithuanian_ci       | utf8    | 204 |         | Yes      |       8 || utf8_slovak_ci           | utf8    | 205 |         | Yes      |       8 || utf8_spanish2_ci         | utf8    | 206 |         | Yes      |       8 || utf8_roman_ci            | utf8    | 207 |         | Yes      |       8 || utf8_persian_ci          | utf8    | 208 |         | Yes      |       8 || utf8_esperanto_ci        | utf8    | 209 |         | Yes      |       8 || utf8_hungarian_ci        | utf8    | 210 |         | Yes      |       8 || utf8_sinhala_ci          | utf8    | 211 |         | Yes      |       8 || utf8_german2_ci          | utf8    | 212 |         | Yes      |       8 || utf8_croatian_ci         | utf8    | 213 |         | Yes      |       8 || utf8_unicode_520_ci      | utf8    | 214 |         | Yes      |       8 || utf8_vietnamese_ci       | utf8    | 215 |         | Yes      |       8 || utf8_general_mysql500_ci | utf8    | 223 |         | Yes      |       1 || 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 || utf8mb4_latvian_ci       | utf8mb4 | 226 |         | Yes      |       8 || utf8mb4_romanian_ci      | utf8mb4 | 227 |         | Yes      |       8 || utf8mb4_slovenian_ci     | utf8mb4 | 228 |         | Yes      |       8 || utf8mb4_polish_ci        | utf8mb4 | 229 |         | Yes      |       8 || utf8mb4_estonian_ci      | utf8mb4 | 230 |         | Yes      |       8 || utf8mb4_spanish_ci       | utf8mb4 | 231 |         | Yes      |       8 || utf8mb4_swedish_ci       | utf8mb4 | 232 |         | Yes      |       8 || utf8mb4_turkish_ci       | utf8mb4 | 233 |         | Yes      |       8 || utf8mb4_czech_ci         | utf8mb4 | 234 |         | Yes      |       8 || utf8mb4_danish_ci        | utf8mb4 | 235 |         | Yes      |       8 || utf8mb4_lithuanian_ci    | utf8mb4 | 236 |         | Yes      |       8 || utf8mb4_slovak_ci        | utf8mb4 | 237 |         | Yes      |       8 || utf8mb4_spanish2_ci      | utf8mb4 | 238 |         | Yes      |       8 || utf8mb4_roman_ci         | utf8mb4 | 239 |         | Yes      |       8 || utf8mb4_persian_ci       | utf8mb4 | 240 |         | Yes      |       8 || utf8mb4_esperanto_ci     | utf8mb4 | 241 |         | Yes      |       8 || utf8mb4_hungarian_ci     | utf8mb4 | 242 |         | Yes      |       8 || utf8mb4_sinhala_ci       | utf8mb4 | 243 |         | Yes      |       8 || utf8mb4_german2_ci       | utf8mb4 | 244 |         | Yes      |       8 || utf8mb4_croatian_ci      | utf8mb4 | 245 |         | Yes      |       8 || utf8mb4_unicode_520_ci   | utf8mb4 | 246 |         | Yes      |       8 || utf8mb4_vietnamese_ci    | utf8mb4 | 247 |         | Yes      |       8 |+--------------------------+---------+-----+---------+----------+---------+53 rows in set (0.00 sec)

查看服务器的字符集和比较规则

mysql> SHOW VARIABLES LIKE '%_server';+----------------------+-------------------+| Variable_name        | Value             |+----------------------+-------------------+| character_set_server | latin1            || collation_server     | latin1_swedish_ci |+----------------------+-------------------+2 rows in set (0.02 sec)

查看当前数据库的字符集和比较规则

mysql> SHOW VARIABLES LIKE '%_database';+------------------------+-------------------+| Variable_name          | Value             |+------------------------+-------------------+| character_set_database | latin1            || collation_database     | latin1_swedish_ci || skip_show_database     | OFF               |+------------------------+-------------------+3 rows in set (0.00 sec)

修改具体数据库的字符集

ALTER DATABASE dbtest DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。utf8_general_ci校对速度快，但准确度稍差。utf8_unicode_ci准确度高，但校对速度稍慢。

⚠️ 一般情况，用utf8_general_ci就够了，但如果你的应用有德语、法语或者俄语，请一定使用utf8_unicode_ci。
⚠️ 修改了数据库的默认字符集和比较规则后，原来已经创建的表格的字符集和比较规则并不会改变，如果需要，那么需单独修改。

  •  
  • ↗️
↗️

MySQL5.7x 主从复制

✇Liu Zijian's Blog

在MySQL中，主从复制（Master-Slave Replication）是一种常用的数据库复制技术，用于将主服务器（Master）的数据实时复制到一个或多个从服务器（Slave）。这有助于负载均衡、备份和高可用性。

前提条件

  • 主服务器和从服务器的MySQL版本应兼容（最好相同版本）。
  • 所有服务器时钟应该同步，可以使用NTP确保时间一致性。
  • 从服务器初始时的数据要与主服务器一致（可以通过备份恢复的方式实现）。

一、配置主服务器（Master）

  1. 编辑主服务器的MySQL配置文件
    打开MySQL主服务器的配置文件 my.cnf 。

    添加或修改以下内容：

    [mysqld]server-id = 1                # 唯一的服务器ID，任意正整数，但需要在集群中唯一log-bin = mysql-bin           # 启用二进制日志，确保日志记录所有数据变更binlog-do-db = my_database    # 要复制的数据库（可以指定多个，也可以省略）
    • server-id：每个服务器都需要有唯一的server-id。
    • log-bin：启用二进制日志，主从复制就是基于二进制日志的。
    • binlog-do-db：指定要复制的数据库，可以指定多个数据库。省略这行会复制所有数据库。

    重启MySQL服务：

    sudo systemctl restart mysqld

  2. 创建用于复制的用户
    在主服务器上登录MySQL并创建一个用于复制的用户。

    CREATE USER 'slave'@'%' IDENTIFIED BY 'password';GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';FLUSH PRIVILEGES;

    这里创建了一个名为slave的用户，密码为password，并授予了复制权限。

  3. 获取主服务器的状态信息
    在主服务器上运行以下命令，记录输出的信息：

    SHOW MASTER STATUS;

    输出类似以下内容：

    +------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 | 12345    | my_database  |                  |+------------------+----------+--------------+------------------+
    • File：二进制日志的文件名。
    • Position：当前日志的偏移量。这两个值将在配置从服务器时使用。

二、配置从服务器（Slave）

  1. 编辑从服务器的MySQL配置文件

    打开从服务器的配置文件my.cnf，添加或修改如下内容：

    [mysqld]server-id = 2             # 唯一的服务器ID，与主服务器不同relay-log = mysql-relay   # 中继日志，用于存储从主服务器接收到的日志

    重启从服务器的MySQL服务：

    sudo systemctl restart mysqld

  2. 初始化从服务器

    登录从服务器的MySQL，并执行以下步骤来设置复制：

    CHANGE MASTER TO    MASTER_HOST='master_ip_address',    MASTER_USER='replicator',    MASTER_PASSWORD='password',    MASTER_LOG_FILE='mysql-bin.000001',  -- 从SHOW MASTER STATUS获取的日志文件名    MASTER_LOG_POS=12345;                -- 从SHOW MASTER STATUS获取的偏移量

    在这里，master_ip_address是主服务器的IP地址，MASTER_LOG_FILE和MASTER_LOG_POS是前面SHOW MASTER STATUS命令中看到的值。

  3. 启动复制

    在从服务器上启动复制进程：

    START SLAVE;

  4. 检查复制状态

    检查从服务器的复制状态，确保复制正常运行：

    SHOW SLAVE STATUS\G;

    查看输出中的两个关键字段：

    • Slave_IO_Running: Yes
    • Slave_SQL_Running: Yes

    如果这两个值都是Yes，说明主从复制正常。如果不是，可以检查错误信息并进行排查。

三、测试主从复制

  1. 在主服务器上创建一个测试表或插入数据：

    USE my_database;CREATE TABLE test_table (id INT PRIMARY KEY, data VARCHAR(50));INSERT INTO test_table VALUES (1, 'Test Data');

  2. 在从服务器上检查该表是否存在以及数据是否同步：

    USE my_database;SELECT * FROM test_table;

    如果能看到相同的表和数据，说明主从复制配置成功。

  •  
  • ↗️