[MySQL]-[操作指令]-设置字符编码和比较规则

基于：Mysql 5.7

一、服务器级别：设置变量

服务器级别的字符编码和比较规则由 系统变量控制

• character_set_server
• collation_server

变量的设置，可以通过 SET 命令进行设置，或者配置在配置文件中，启动的时候直接使用配置的编码格式和比较规则

1.1、命令行设置

mysql> SET character_set_server=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'character_set_server';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_server | utf8  |
+----------------------+-------+
1 row in set (0.00 sec)

比较规则变量设置，同上。

1.2、配置文件配置

[server]
character_set_server=gbk
collation_server=gbk_chiness_ci

二、数据库级别：创建/修改数据库时设置字符编码和比较规则

命令

案例

创建数据库同时指定编码格式

mysql> CREATE DATABASE character_demo_db CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW CREATE DATABASE character_demo_db;
+-------------------+----------------------------------------------------------------------------+
| Database          | Create Database                                                            |
+-------------------+----------------------------------------------------------------------------+
| character_demo_db | CREATE DATABASE `character_demo_db` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-------------------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改数据库的编码格式

mysql> ALTER DATABASE character_demo_db CHARACTER SET gbk;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW CREATE DATABASE character_demo_db;
+-------------------+---------------------------------------------------------------------------+
| Database          | Create Database                                                           |
+-------------------+---------------------------------------------------------------------------+
| character_demo_db | CREATE DATABASE `character_demo_db` /*!40100 DEFAULT CHARACTER SET gbk */ |
+-------------------+---------------------------------------------------------------------------+
1 row in set (0.00 sec

三、表级别：创建/修改表

命令

案例：

mysql> USE character_demo_db;
Database changed

## 创建
mysql> CREATE TABLE table1(
    -> c1 VARCHAR(10)
    -> ) CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE table1\G;
*************************** 1. row ***************************
       Table: table1
Create Table: CREATE TABLE `table1` (
  `c1` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

## 修改
mysql> ALTER TABLE table1 CHARACTER SET gbk;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE table1\G;
*************************** 1. row ***************************
       Table: table1
Create Table: CREATE TABLE `table1` (
  `c1` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

四、列级别：创建/修改列

命令

案例

## 创建表时字段指定
mysql> CREATE TABLE table2(
    -> clo1 VARCHAR(10) CHARACTER SET gbk COLLATE gbk_chinese_ci 
    -> ) CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW CREATE TABLE table2\G;
*************************** 1. row ***************************
       Table: table2
Create Table: CREATE TABLE `table2` (
  `clo1` varchar(10) CHARACTER SET gbk DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

## 表字段修改/新增质地的那个
mysql> ALTER TABLE table2 MODIFY clo1 VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE table2\G;
*************************** 1. row ***************************
       Table: table2
Create Table: CREATE TABLE `table2` (
  `clo1` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)