如何将 MySQL 数据库字符集和排序规则转换为 UTF-8?

mysqlmysqli database更新于 2024/1/24 16:56:00

首先,我们将借助 version() 函数检查当前正在使用的 MySQL 版本 −

查询如下 −

mysql> SELECT version();

以下是输出 −

+-----------+
| version() |
+-----------+
| 8.0.12    |
+-----------+
1 row in set (0.00 sec)

如您在上面的输出中看到的那样,正在使用版本 8.0.12。现在,我们可以使用以下语法检查当前的字符编码 −

SELECT CCSA.character_set_name FROM information_schema.`TABLES`T,information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name
-> =T.table_collation AND T.table_schema = "yourDatabaseName" AND T.table_name =
"yourTableName";

应用上述查询 −

mysql> SELECT CCSA.character_set_name FROM information_schema.`TABLES`T,information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name
-> =T.table_collation AND T.table_schema = "sample" AND T.table_name = "mytable";

输出结果如下:

+--------------------+
| CHARACTER_SET_NAME |
+--------------------+
| utf8mb4            |
+--------------------+
1 row in set (0.00 sec)

下面是我们为数据库设置 utf8 的查询 −

mysql> ALTER DATABASE sample CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected, 1 warning (0.27 sec)

让我们检查一下 −

mysql> SELECT CCSA.character_set_name FROM information_schema.`TABLES`T,information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name
-> =T.table_collation AND T.table_schema = "sample" AND T.table_name = "mytable";

以下是输出 −

+--------------------+
| CHARACTER_SET_NAME |
+--------------------+
| utf8mb4            |
+--------------------+
1 row in set (0.00 sec)

相关文章