主页 / dba / 问题 / 4227
Accepted
Mokus
Asked: 2011-08-02 06:31:38 +0800 CST

表中的字符集编码

  • 772

我有以下数据：Trollhättan

  • 如果我打印出来并将标题设置为utf-8，那么这就是浏览器中的输出。

  • 如果我没有utf-8在标题中设置，Trollhättan. 但是，当我将数据存储在数据库中并进行检查时phpmyadmin，我得到以下字符串：Trollhättan。

  • 当我将标头设置为 时utf-8，我得到了这个字符串：Trollhättan在这个旁边Trollhättan。

既然是表latin1_swedish_ci，我一定要在表中使用UTF-8吗？

mysql character-set

2 个回答

  1. 您需要确保当您“将数据存储在数据库中”时，您使用的客户端使用的是utf8。然后将 http 标头设置为 utf8 完成往返。

    令人惊讶的是，数据库中使用什么字符集并不重要（尽管使用 utf8 可能也很方便）。

    • 1
  2. Best Answer

    这是UTF-8下的排序规则列表

    mysql> select * from information_schema.collations where CHARACTER_SET_NAME = 'utf8';
+--------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME     | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_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 |
+--------------------+--------------------+-----+------------+-------------+---------+
22 rows in set (0.03 sec)

    latin1_swedish_ci属于 latin1

    mysql> select * from information_schema.collations where COLLATION_NAME = 'latin1_swedish_ci';
+-------------------+--------------------+----+------------+-------------+---------+
| COLLATION_NAME    | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+-------------------+--------------------+----+------------+-------------+---------+
| latin1_swedish_ci | latin1             |  8 | Yes        | Yes         |       1 |
+-------------------+--------------------+----+------------+-------------+---------+
1 row in set (0.03 sec)

    与您的归类最接近的 utf8 是 ID 200

    mysql> select * from information_schema.collations where ID = 200;
+-----------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME  | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |
+-----------------+--------------------+-----+------------+-------------+---------+
| utf8_swedish_ci | utf8               | 200 |            | Yes         |       8 |
+-----------------+--------------------+-----+------------+-------------+---------+
1 row in set (0.00 sec)

    警告

    您可能需要尝试使用 latin1，更改表中单个列的字符集和排序规则，或者两者都可能。至少，在 phpmyadmin 中使用 latin1 进行显示。

    您可以调整并尝试使用ALTER DATABASE使整个数据库成为特定的字符集和排序规则。

    ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

    确保备份数据库并将其加载到开发/暂存数据库中，然后针对开发/暂存运行 ALTER DATABASE。

    • 1

相关问题