tidb教程FG070-TiDB字符集与时区配置

本文档风哥主要介绍TiDB字符集与时区配置的相关内容，包括字符集的概念、类型、配置方法，时区的概念、配置方法，以及实战案例和故障处理等，风哥教程参考TiDB官方文档字符集与时区相关内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。更多视频教程www.fgedu.net.cn

Part01-基础概念与理论知识

1.1 字符集的概念

字符集是指一组字符的集合，以及字符的编码规则：

字符集的定义：

字符集合：包含所有可能使用的字符
编码规则：将字符映射到二进制数据的规则
排序规则：定义字符的排序顺序

1.2 时区的概念

时区是指地球上不同地区使用的时间标准：

时区的定义：

UTC：协调世界时，是全球标准时间
本地时区：各地区根据地理位置设置的时间
夏令时：部分地区在夏季调整时间以充分利用日照

1.3 TiDB支持的字符集

TiDB支持多种字符集，包括：

# TiDB支持的字符集

## 1. 常用字符集
– **utf8mb4**：支持UTF-8编码，最多可存储4字节的字符，包括emoji表情
– **utf8**：支持UTF-8编码，最多可存储3字节的字符
– **latin1**：ISO-8859-1编码，支持西欧语言
– **gbk**：GB2312的扩展，支持中文字符
– **gb2312**：支持中文字符，范围比gbk小
– **binary**：二进制字符集，按字节存储

## 2. 字符集特性
– **utf8mb4**：
– 支持所有Unicode字符
– 存储空间较大
– 推荐使用
– **utf8**：
– 支持部分Unicode字符
– 存储空间较小
– 不支持emoji表情
– **latin1**：
– 只支持西欧语言
– 存储空间最小
– 不支持中文字符风哥提示：
– **gbk**：
– 支持中文字符
– 存储空间适中
– 兼容性较好
– **gb2312**：
– 支持部分中文字符
– 存储空间较小
– 兼容性一般
– **binary**：
– 按字节存储
– 不进行字符转换
– 适合存储二进制数据

## 3. 排序规则
– **utf8mb4_general_ci**：通用排序规则，不区分大小写
– **utf8mb4_bin**：二进制排序规则，区分大小写
– **utf8mb4_unicode_ci**：Unicode排序规则，更准确但性能稍差
– **gbk_chinese_ci**：GBK中文排序规则
– **latin1_swedish_ci**：Latin1瑞典排序规则

1.4 TiDB支持的时区

TiDB支持多种时区配置，包括：

# TiDB支持的时区

## 1. 时区类型
– **系统时区**：TiDB服务器所在系统的时区
– **TiDB时区**：TiDB配置文件中设置的时区
– **会话时区**：当前会话使用的时区

## 2. 时区表示方法
– **UTC偏移**：如 +08:00，-05:00 等
– **时区名称**：如 Asia/Shanghai，America/New_York 等
– **系统时区**：使用系统默认时区

## 3. 时区文件
– **位置**：TiDB使用系统的时区文件
– **路径**：通常在 /usr/share/zoneinfo 目录
– **配置**：通过 tidb_time_zone 参数配置

## 4. 时区影响
– **时间函数**：如 NOW()，CURRENT_TIMESTAMP() 等
– **日期时间类型**：TIMESTAMP 类型会根据时区进行转换
– **时间比较**：不同时区的时间比较结果可能不同

风哥提示：字符集和时区配置是TiDB数据库的重要组成部分，直接影响数据的存储和查询结果。学习交流加群风哥微信: itpux-com

Part02-生产环境规划与建议

2.1 字符集规划

在生产环境中，字符集的选择需要考虑以下因素：

# 字符集规划

## 1. 考虑因素
– **业务需求**：
– 是否需要支持中文字符
– 是否需要支持emoji表情
– 是否需要支持其他语言
– **存储空间**：
– utf8mb4 占用空间较大
– latin1 占用空间较小
– **性能影响**：
– 字符集转换会影响性能
– 不同字符集的排序性能不同
– **兼容性**：
– 应用程序对字符集的支持
– 与其他系统的兼容性

## 2. 推荐字符集
– **默认推荐**：utf8mb4
– 支持所有Unicode字符
– 兼容性最好
– 未来扩展性强
– **特殊场景**：
– 仅存储西欧语言：latin1
– 仅存储中文字符：gbk
– 存储二进制数据：binary

## 3. 字符集设置层级
– **服务器级**：通过配置文件设置默认字符集
– **数据库级**：在创建数据库时指定字符集
– **表级**：在创建表时指定字符集
– **列级**：在定义列时指定字符集

## 4. 字符集一致性
– **保持一致**：尽量在整个系统中使用相同的字符集
– **避免转换**：减少字符集转换带来的性能损耗
– **统一配置**：在应用程序和数据库中使用相同的字符集

2.2 时区规划

在生产环境中，时区的选择需要考虑以下因素：

学习交流加群风哥QQ113257174

# 时区规划

## 1. 考虑因素
– **业务需求**：
– 业务所在地区的时区
– 是否需要处理跨时区业务
– 报表和日志的时间一致性
– **系统架构**：
– 分布式系统的时区一致性
– 应用服务器和数据库服务器的时区同步
– **数据迁移**：
– 时区变更对历史数据的影响
– 数据迁移时的时区转换

## 2. 推荐时区设置
– **默认推荐**：Asia/Shanghai（中国标准时间）
– 适合中国地区的业务
– 与系统默认时区一致
– **跨时区业务**：
– 统一使用UTC时区
– 在应用层进行时区转换
– **特殊场景**：
– 国际业务：根据业务所在地区设置时区
– 金融业务：使用UTC时区确保时间一致性

## 3. 时区设置层级
– **系统级**：设置操作系统的时区
– **TiDB级**：通过配置文件设置TiDB的时区
– **会话级**：通过SET time_zone命令设置当前会话的时区

## 4. 时区一致性
– **服务器同步**：确保所有服务器使用相同的时区
– **应用配置**：确保应用程序使用与数据库相同的时区
– **数据处理**：在处理时间数据时考虑时区因素

2.3 配置建议

生产环境中的配置建议：

字符集配置建议：

服务器级：设置默认字符集为 utf8mb4
数据库级：创建数据库时指定字符集为 utf8mb4
表级：创建表时指定字符集为 utf8mb4
列级：对于特殊字段，可根据需要指定不同的字符集

时区配置建议：

系统级：设置操作系统时区为 Asia/Shanghai
TiDB级：在配置文件中设置 tidb_time_zone = “Asia/Shanghai”
会话级：在应用程序连接数据库时设置会话时区
应用级：确保应用程序使用与数据库相同的时区

Part03-生产环境项目实施方案

3.1 字符集配置

3.1.1 服务器级字符集配置

# 服务器级字符集配置

## 1. 修改TiDB配置文件
$ vim /tidb/app/tidb/conf/tidb.toml

# 添加或修改以下配置
[server]
character-set-server = “utf8mb4”
collation-server = “utf8mb4_general_ci”

## 2. 重启TiDB服务
$ systemctl restart tidb

## 3. 验证配置
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW VARIABLES LIKE ‘character_set_server’;”

$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW VARIABLES LIKE ‘collation_server’;”

3.1.2 数据库级字符集配置

# 数据库级字符集配置

## 1. 创建数据库时指定字符集
$ mysql -h 192.168.1.10 -P 4000 -u root -e “CREATE DATABASE fgedudb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;”

## 2. 修改现有数据库的字符集
$ mysql -h 192.168.1.10 -P 4000 -u root -e “ALTER DATABASE fgedudb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;”

## 3. 验证数据库字符集
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SELECT schema_name, default_character_set_name, default_collation_name FROM information_schema.schemata WHERE schema_name = ‘fgedudb’;”

3.1.3 表级字符集配置

# 表级字符集配置

## 1. 创建表时指定字符集
$ mysql -h 192.168.1.10 -P 4000 -u root -e “CREATE TABLE fgedu_users (id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100)) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;”

## 2. 修改现有表的字符集
$ mysql -h 192.168.1.10 -P 4000 -u root -e “ALTER TABLE fgedu_users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;”

## 3. 验证表字符集
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW CREATE TABLE fgedu_users;”

# 输出示例
+————+—————————————————————————————————————————————————————————————————————————————————-+
| Table | Create Table |
+————+—————————————————————————————————————————————————————————————————————————————————-+
| fgedu_users | CREATE TABLE `fgedu_users` (
`id` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+————+—————————————————————————————————————————————————————————————————————————————————-+

3.1.4 列级字符集配置

# 列级字符集配置

## 1. 创建表时指定列字符集
$ mysql -h 192.168.1.10 -P 4000 -u root -e “CREATE TABLE fgedu_products (id INT PRIMARY KEY, name VARCHAR(100) CHARACTER SET utf8mb4, description TEXT CHARACTER SET gbk);”

## 2. 修改现有列的字符集
$ mysql -h 192.168.1.10 -P 4000 -u root -e “ALTER TABLE fgedu_products MODIFY description TEXT CHARACTER SET utf8mb4;”

## 3. 验证列字符集
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW FULL COLUMNS FROM fgedu_products;”

3.2 时区配置

3.2.1 系统级时区配置

# 系统级时区配置

## 1. 查看当前系统时区
$ timedatectl

# 输出示例
Local time: Mon 2024-01-01 10:00:00 CST
Universal time: Mon 2024-01-01 02:00:00 UTC
RTC time: Mon 2024-01-01 02:00:00
Time zone: Asia/Shanghai (CST, +0800)
System clock synchronized: yes
NTP service: active
RTC in local TZ: no

## 2. 设置系统时区
$ timedatectl set-timezone Asia/Shanghai

## 3. 验证系统时区
$ timedatectl

3.2.2 TiDB级时区配置

# TiDB级时区配置

## 1. 修改TiDB配置文件
$ vim /tidb/app/tidb/conf/tidb.toml

# 添加或修改以下配置
[server]
tidb_time_zone = “Asia/Shanghai”

## 2. 重启TiDB服务
$ systemctl restart tidb

## 3. 验证TiDB时区
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW VARIABLES LIKE ‘tidb_time_zone’;”

$ mysql -h 192.168.1.10 -P 4000 -u root -e “SELECT NOW();”

# 输出示例
+———————+
| NOW() |
+———————+
| 2024-01-01 10:00:00 |
+———————+

3.2.3 会话级时区配置

# 会话级时区配置

## 1. 查看当前会话时区
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SELECT @@session.time_zone;”

# 输出示例
+———————+
| @@session.time_zone |
+———————+
| Asia/Shanghai |
+———————+

## 2. 设置会话时区为UTC
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SET time_zone = ‘+00:00’;”

## 3. 验证会话时区
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SELECT @@session.time_zone;”

# 输出示例
+———————+
| @@session.time_zone |
+———————+
| +00:00 |
+———————+

## 4. 验证时间函数
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SELECT NOW();”

# 输出示例
+———————+
| NOW() |
+———————+
| 2024-01-01 02:00:00 |
+———————+

## 5. 恢复会话时区
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SET time_zone = ‘Asia/Shanghai’;”

3.3 验证步骤

字符集和时区配置的验证步骤：

# 验证步骤

## 1. 字符集验证

### 1.1 服务器字符集
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW VARIABLES LIKE ‘character_set_%’;”

### 1.2 数据库字符集
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SELECT schema_name, default_character_set_name, default_collation_name FROM information_schema.schemata;”

### 1.3 表字符集
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW TABLE STATUS FROM fgedudb;”

### 1.4 列字符集
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW FULL COLUMNS FROM fgedu_users;”

## 2. 时区验证

### 2.1 系统时区
$ timedatectl

### 2.2 TiDB时区
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW VARIABLES LIKE ‘%time_zone%’;”

### 2.3 时间函数验证
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SELECT NOW(), UTC_TIMESTAMP(), CURRENT_TIMESTAMP();”

# 输出示例
+———————+———————+———————+
| NOW() | UTC_TIMESTAMP() | CURRENT_TIMESTAMP() |
+———————+———————+———————+
| 2024-01-01 10:00:00 | 2024-01-01 02:00:00 | 2024-01-01 10:00:00 |
+———————+———————+———————+

### 2.4 时区转换验证
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SELECT CONVERT_TZ(NOW(), ‘Asia/Shanghai’, ‘UTC’);”

# 输出示例
+——————————————–+
| CONVERT_TZ(NOW(), ‘Asia/Shanghai’, ‘UTC’) |
+——————————————–+
| 2024-01-01 02:00:00 |
+——————————————–+

风哥提示：字符集和时区配置需要在系统、数据库和应用程序之间保持一致，否则可能会导致数据显示异常或时间计算错误。from tidb视频:www.itpux.com

Part04-生产案例与实战讲解

4.1 字符集配置实战

4.1.1 字符集配置实战案例

# 字符集配置实战案例

## 1. 环境信息
– **TiDB版本**：6.1.0
– **操作系统**：Oracle Linux 9.3
– **数据库**：fgedudb
– **目标字符集**：utf8mb4

## 2. 配置步骤

### 2.1 修改TiDB配置文件
$ vim /tidb/app/tidb/conf/tidb.toml

# 添加以下配置
[server]
character-set-server = “utf8mb4”
collation-server = “utf8mb4_general_ci”

### 2.2 重启TiDB服务
$ systemctl restart tidb

### 2.3 创建数据库
$ mysql -h 192.168.1.10 -P 4000 -u root -e “CREATE DATABASE fgedudb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;”

### 2.4 创建表
$ mysql -h 192.168.1.10 -P 4000 -u root -e “USE fgedudb; CREATE TABLE fgedu_users (id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100), address VARCHAR(255));”

### 2.5 插入测试数据
$ mysql -h 192.168.1.10 -P 4000 -u root -e “USE fgedudb; INSERT INTO fgedu_users (id, name, email, address) VALUES (1, ‘张三’, ‘zhangsan@fgedu.net.cn’, ‘北京市朝阳区’), (2, ‘李四’, ‘lisi@fgedu.net.cn’, ‘上海市浦东新区’), (3, ‘王小明😊’, ‘wangxiaoming@fgedu.net.cn’, ‘广州市天河区’);”

### 2.6 验证数据
$ mysql -h 192.168.1.10 -P 4000 -u root -e “USE fgedudb; SELECT * FROM fgedu_users;”

# 输出示例
+—-+———–+————————-+——————-+
| id | name | email | address |
+—-+———–+————————-+——————-+
| 1 | 张三 | zhangsan@fgedu.net.cn | 北京市朝阳区 |
| 2 | 李四 | lisi@fgedu.net.cn | 上海市浦东新区 |
| 3 | 王小明😊 | wangxiaoming@fgedu.net.cn | 广州市天河区 |
+—-+———–+————————-+——————-+

## 3. 验证结果
– **字符集设置**：成功设置为 utf8mb4
– **emoji支持**：成功存储包含emoji表情的数据
– **中文支持**：成功存储中文字符
– **查询结果**：数据显示正常

## 4. 常见问题处理
– **问题**：插入包含emoji表情的数据失败
**原因**：字符集不是utf8mb4
**解决**：将字符集修改为utf8mb4

– **问题**：中文显示乱码
**原因**：字符集不一致
**解决**：确保数据库、表、列的字符集都是utf8mb4

– **问题**：排序结果不正确
**原因**：排序规则设置不当
**解决**：选择合适的排序规则，如utf8mb4_general_ci

4.2 时区配置实战

4.2.1 时区配置实战案例

# 时区配置实战案例

## 1. 环境信息
– **TiDB版本**：6.1.0
– **操作系统**：Oracle Linux 9.3
– **目标时区**：Asia/Shanghai

## 2. 配置步骤

### 2.1 设置系统时区
$ timedatectl set-timezone Asia/Shanghai
$ timedatectl

### 2.2 修改TiDB配置文件
$ vim /tidb/app/tidb/conf/tidb.toml

# 添加以下配置
[server]
tidb_time_zone = “Asia/Shanghai”

### 2.3 重启TiDB服务
$ systemctl restart tidb

### 2.4 验证TiDB时区
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW VARIABLES LIKE ‘%time_zone%’;”

### 2.5 测试时间函数
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SELECT NOW(), UTC_TIMESTAMP(), CURRENT_TIMESTAMP();”

### 2.6 测试时区转换
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SELECT CONVERT_TZ(NOW(), ‘Asia/Shanghai’, ‘America/New_York’);”

# 输出示例
+—————————————————+
| CONVERT_TZ(NOW(), ‘Asia/Shanghai’, ‘America/New_York’) |
+—————————————————+
| 2023-12-31 21:00:00 |
+—————————————————+

### 2.7 创建表并插入时间数据
$ mysql -h 192.168.1.10 -P 4000 -u root -e “USE fgedudb; CREATE TABLE fgedu_orders (id INT PRIMARY KEY, order_time DATETIME, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP);”
$ mysql -h 192.168.1.10 -P 4000 -u root -e “USE fgedudb; INSERT INTO fgedu_orders (id, order_time) VALUES (1, NOW()), (2, NOW() – INTERVAL 1 HOUR), (3, NOW() – INTERVAL 1 DAY);”

### 2.8 验证时间数据
$ mysql -h 192.168.1.10 -P 4000 -u root -e “USE fgedudb; SELECT * FROM fgedu_orders;”

# 输出示例
+—-+———————+———————+
| id | order_time | create_time |
+—-+———————+———————+
| 1 | 2024-01-01 10:00:00 | 2024-01-01 10:00:00 |
| 2 | 2024-01-01 09:00:00 | 2024-01-01 10:00:00 |
| 3 | 2023-12-31 10:00:00 | 2024-01-01 10:00:00 |
+—-+———————+———————+

## 3. 验证结果
– **系统时区**：成功设置为 Asia/Shanghai
– **TiDB时区**：成功设置为 Asia/Shanghai
– **时间函数**：返回正确的本地时间
– **时区转换**：正确转换不同时区的时间
– **时间数据**：正确存储和显示时间数据

## 4. 常见问题处理
– **问题**：时间函数返回的时间与系统时间不一致
**原因**：TiDB时区设置与系统时区不一致
**解决**：确保TiDB时区设置与系统时区一致

– **问题**：TIMESTAMP类型数据显示不正确
**原因**：时区转换问题
**解决**：确保会话时区与TiDB时区一致

– **问题**：跨时区业务时间计算错误
**原因**：不同时区的时间转换问题
**解决**：统一使用UTC时区存储时间，在应用层进行时区转换

4.3 故障处理

字符集和时区配置过程中的故障处理：

# 故障处理

## 1. 字符集相关故障

### 1.1 中文显示乱码
– **问题描述**：查询结果中中文字符显示为乱码
– **原因分析**：字符集不一致，可能是客户端、连接或数据库的字符集设置不当
– **解决方案**：
– 检查客户端字符集：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW VARIABLES LIKE ‘character_set_client’;”
– 检查连接字符集：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW VARIABLES LIKE ‘character_set_connection’;”
– 检查结果字符集：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW VARIABLES LIKE ‘character_set_results’;”
– 设置连接字符集：
$ mysql -h 192.168.1.10 -P 4000 -u root –default-character-set=utf8mb4

### 1.2 Emoji表情存储失败
– **问题描述**：插入包含emoji表情的数据失败，报错”Incorrect string value”
– **原因分析**：字符集不是utf8mb4，无法存储4字节的emoji字符
– **解决方案**：
– 修改数据库字符集：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “ALTER DATABASE fgedudb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;”
– 修改表字符集：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “ALTER TABLE fgedu_users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;”
– 修改列字符集：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “ALTER TABLE fgedu_users MODIFY name VARCHAR(100) CHARACTER SET utf8mb4;”

### 1.3 排序结果不正确
– **问题描述**：中文字符排序结果不符合预期
– **原因分析**：排序规则设置不当，如使用了binary排序规则
– **解决方案**：
– 修改排序规则：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “ALTER TABLE fgedu_users COLLATE utf8mb4_general_ci;”
– 验证排序结果：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “USE fgedudb; SELECT * FROM fgedu_users ORDER BY name;”

## 2. 时区相关故障

### 2.1 时间函数返回错误时间
– **问题描述**：NOW()函数返回的时间与系统时间不一致
– **原因分析**：TiDB时区设置与系统时区不一致
– **解决方案**：
– 检查系统时区：
$ timedatectl
– 检查TiDB时区：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW VARIABLES LIKE ‘tidb_time_zone’;”
– 修改TiDB时区配置：
$ vim /tidb/app/tidb/conf/tidb.toml
[server]
tidb_time_zone = “Asia/Shanghai”
– 重启TiDB服务：
$ systemctl restart tidb

### 2.2 TIMESTAMP类型数据显示错误
– **问题描述**：TIMESTAMP类型数据显示的时间与插入时的时间不一致
– **原因分析**：会话时区与TiDB时区不一致，导致时区转换错误
– **解决方案**：
– 检查会话时区：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SELECT @@session.time_zone;”
– 设置会话时区：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SET time_zone = ‘Asia/Shanghai’;”
– 验证时间数据：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SELECT * FROM fgedu_orders;”

### 2.3 跨时区业务时间计算错误
– **问题描述**：不同时区的业务操作时间计算错误
– **原因分析**：时区设置不一致，导致时间转换错误
– **解决方案**：
– 统一使用UTC时区存储时间：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SET time_zone = ‘+00:00’;”
– 在应用层进行时区转换：
使用应用程序的时区转换函数将UTC时间转换为本地时间
– 验证时间计算：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SELECT CONVERT_TZ(order_time, ‘UTC’, ‘Asia/Shanghai’) FROM fgedu_orders;”

## 3. 其他故障

### 3.1 配置文件修改不生效
– **问题描述**：修改了TiDB配置文件，但配置不生效
– **原因分析**：配置文件路径错误，或TiDB服务未重启
– **解决方案**：
– 检查配置文件路径：
$ ls -la /tidb/app/tidb/conf/tidb.toml
– 重启TiDB服务：
$ systemctl restart tidb
– 验证配置：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW VARIABLES LIKE ‘character_set_server’;”

### 3.2 系统时区修改后TiDB时区未更新
– **问题描述**：修改了系统时区，但TiDB时区未更新
– **原因分析**：TiDB服务未重启，或TiDB配置文件中明确指定了时区
– **解决方案**：
– 重启TiDB服务：
$ systemctl restart tidb
– 检查TiDB时区配置：
$ vim /tidb/app/tidb/conf/tidb.toml
– 验证TiDB时区：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW VARIABLES LIKE ‘tidb_time_zone’;”

Part05-风哥经验总结与分享

5.1 最佳实践

字符集和时区配置的最佳实践：

字符集最佳实践：

统一使用utf8mb4：支持所有Unicode字符，包括emoji表情
保持字符集一致性：确保数据库、表、列的字符集一致
在应用层设置字符集：在应用程序连接数据库时设置正确的字符集
定期检查字符集设置：确保字符集设置未被意外修改
测试emoji支持：在生产环境部署前测试emoji表情的存储和显示

时区最佳实践：

统一时区设置：确保系统、TiDB和应用程序使用相同的时区
使用时区名称：使用Asia/Shanghai等时区名称，而不是UTC偏移
跨时区业务使用UTC：对于跨时区业务，建议使用UTC时区存储时间
在应用层处理时区转换：将时间存储为UTC，在应用层转换为本地时间
定期同步系统时间：使用NTP服务确保系统时间准确

5.2 常见问题与解决方案

字符集和时区配置的常见问题与解决方案：

# 常见问题与解决方案

## 1. 字符集相关问题

### 1.1 中文显示乱码
– **问题**：查询结果中中文字符显示为乱码
– **原因**：字符集不一致
– **解决**：
– 确保数据库字符集为utf8mb4
– 确保客户端连接字符集为utf8mb4
– 确保应用程序使用utf8mb4编码

### 1.2 Emoji表情存储失败
– **问题**：插入包含emoji表情的数据失败
– **原因**：字符集不是utf8mb4
– **解决**：
– 将数据库、表、列的字符集修改为utf8mb4
– 确保连接字符集为utf8mb4

### 1.3 排序结果不正确
– **问题**：中文字符排序结果不符合预期
– **原因**：排序规则设置不当
– **解决**：
– 使用utf8mb4_general_ci或utf8mb4_unicode_ci排序规则
– 避免使用binary排序规则

## 2. 时区相关问题

### 2.1 时间函数返回错误时间
– **问题**：NOW()函数返回的时间与系统时间不一致
– **原因**：TiDB时区设置与系统时区不一致
– **解决**：
– 确保TiDB时区设置与系统时区一致
– 重启TiDB服务使配置生效

### 2.2 TIMESTAMP类型数据显示错误
– **问题**：TIMESTAMP类型数据显示的时间与插入时的时间不一致
– **原因**：会话时区与TiDB时区不一致
– **解决**：
– 确保会话时区与TiDB时区一致
– 在应用程序连接数据库时设置正确的时区

### 2.3 跨时区业务时间计算错误
– **问题**：不同时区的业务操作时间计算错误
– **原因**：时区设置不一致
– **解决**：
– 统一使用UTC时区存储时间
– 在应用层进行时区转换
– 确保所有服务器使用相同的时区设置

## 3. 配置管理问题

### 3.1 配置文件修改不生效
– **问题**：修改了配置文件，但配置不生效
– **原因**：TiDB服务未重启
– **解决**：
– 重启TiDB服务
– 验证配置是否正确应用

### 3.2 多节点配置不一致
– **问题**：集群中不同节点的字符集或时区配置不一致
– **原因**：配置文件修改时未同步到所有节点
– **解决**：
– 确保所有节点的配置文件一致
– 重启所有节点的TiDB服务
– 验证所有节点的配置是否一致

5.3 配置技巧

字符集和时区配置的实用技巧：

# 配置技巧

## 1. 字符集配置技巧

### 1.1 批量修改字符集
– **修改数据库字符集**：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “ALTER DATABASE fgedudb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;”

– **批量修改表字符集**：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SELECT CONCAT(‘ALTER TABLE ‘, table_name, ‘ CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;’) FROM information_schema.tables WHERE table_schema = ‘fgedudb’;”

– **批量修改列字符集**：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SELECT CONCAT(‘ALTER TABLE ‘, table_name, ‘ MODIFY ‘, column_name, ‘ ‘, column_type, ‘ CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;’) FROM information_schema.columns WHERE table_schema = ‘fgedudb’ AND data_type IN (‘varchar’, ‘text’);”

### 1.2 字符集验证技巧
– **检查数据库字符集**：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SELECT schema_name, default_character_set_name, default_collation_name FROM information_schema.schemata;”

– **检查表字符集**：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW TABLE STATUS FROM fgedudb;”

– **检查列字符集**：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW FULL COLUMNS FROM fgedu_users;”

– **测试emoji支持**：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “USE fgedudb; INSERT INTO fgedu_users (id, name) VALUES (4, ‘测试😊’); SELECT * FROM fgedu_users WHERE id = 4;”

## 2. 时区配置技巧

### 2.1 时区设置技巧
– **设置系统时区**：
$ timedatectl set-timezone Asia/Shanghai

– **设置TiDB时区**：
$ vim /tidb/app/tidb/conf/tidb.toml
[server]
tidb_time_zone = “Asia/Shanghai”

– **设置会话时区**：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SET time_zone = ‘Asia/Shanghai’;”

– **在应用程序中设置时区**：
– JDBC URL: jdbc:mysql://192.168.1.10:4000/fgedudb?serverTimezone=Asia/Shanghai
– PHP: date_default_timezone_set(‘Asia/Shanghai’);
– Python: import os; os.environ[‘TZ’] = ‘Asia/Shanghai’; time.tzset()

### 2.2 时区验证技巧
– **检查系统时区**：
$ timedatectl

– **检查TiDB时区**：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW VARIABLES LIKE ‘%time_zone%’;”

– **测试时间函数**：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SELECT NOW(), UTC_TIMESTAMP(), CURRENT_TIMESTAMP();”

– **测试时区转换**：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SELECT CONVERT_TZ(NOW(), ‘Asia/Shanghai’, ‘UTC’);”

### 2.3 跨时区处理技巧
– **使用UTC存储时间**：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SET time_zone = ‘+00:00’; INSERT INTO fgedu_orders (id, order_time) VALUES (4, NOW());”

– **在应用层转换时区**：
– Java: ZonedDateTime.now(ZoneId.of(“UTC”)).withZoneSameInstant(ZoneId.of(“Asia/Shanghai”))
– PHP: date(‘Y-m-d H:i:s’, strtotime($utc_time . ‘ UTC’));
– Python: datetime.datetime.fromisoformat(utc_time).astimezone(timezone(‘Asia/Shanghai’))

– **处理夏令时**：
– 使用时区名称（如Asia/Shanghai）而不是UTC偏移，自动处理夏令时
– 测试夏令时转换：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SELECT CONVERT_TZ(‘2024-07-01 10:00:00’, ‘Asia/Shanghai’, ‘UTC’);”

## 3. 自动化配置技巧

### 3.1 编写配置脚本
– **字符集配置脚本**：
#!/bin/bash
# charset_config.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

mysql -h 192.168.1.10 -P 4000 -u root -e “ALTER DATABASE fgedudb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;”

tables=$(mysql -h 192.168.1.10 -P 4000 -u root -e “SELECT table_name FROM information_schema.tables WHERE table_schema = ‘fgedudb’;” -sN)
for table in $tables; do
mysql -h 192.168.1.10 -P 4000 -u root -e “ALTER TABLE fgedudb.$table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;”
done

– **时区配置脚本**：
#!/bin/bash
# timezone_config.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

timedatectl set-timezone Asia/Shanghai

sed -i ‘s/tidb_time_zone.*/tidb_time_zone = “Asia\/Shanghai”/’ /tidb/app/tidb/conf/tidb.toml

systemctl restart tidb

mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW VARIABLES LIKE ‘%time_zone%’;”

### 3.2 监控配置状态
– **监控字符集**：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW VARIABLES LIKE ‘character_set_%’;” > /tidb/monitor/charset_status.txt

– **监控时区**：
$ mysql -h 192.168.1.10 -P 4000 -u root -e “SHOW VARIABLES LIKE ‘%time_zone%’;” > /tidb/monitor/timezone_status.txt

– **定期检查**：
添加到crontab，定期检查配置状态
$ crontab -e
0 0 * * * /tidb/scripts/check_config.sh

风哥提示：字符集和时区配置是TiDB数据库的基础配置，需要在系统设计阶段就考虑清楚，避免后期修改带来的风险。更多学习教程公众号风哥教程itpux_com

总结：

本文档详细介绍了TiDB字符集与时区配置的相关内容，包括字符集的概念、类型、配置方法，时区的概念、配置方法，以及实战案例和故障处理等。通过本文档的学习，读者可以掌握TiDB字符集与时区配置的完整流程和最佳实践，确保系统的稳定性和数据的正确性。学习交流加群风哥QQ113257174

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html