MySQL 更改 phpmyadmin 中的默认排序规则

change default collation in phpmyadmin

提问by Sergio del Amo

It seems to me that phpMyAdmin imports tables by default with collation latin1_swedish_ci, how i change this?

在我看来，phpMyAdmin 默认使用排序规则 latin1_swedish_ci 导入表，我该如何更改？

采纳答案by Eran Galperin

In your Mysql configuration change the default-character-set operative under the [mysqld] tab. For example:

在您的 Mysql 配置中，更改 [mysqld] 选项卡下的默认字符集操作。例如：

[mysqld]
default-character-set=utf8

Don't forget to restart your Mysql server afterwards for the changes to take effect.

之后不要忘记重新启动 Mysql 服务器以使更改生效。

回答by Lamy

For Linux:

对于 Linux：

1. You need to have access to the MySQL config file.
   The location can vary from /etc/mysql/my.cnfto ~/my.cnf(user directory).

2. Add the following lines in the section [mysqld]:

   collation_server = utf8_unicode_ci
   character_set_server=utf8
   

3. Restart the server:

   service mysqld restart
   

1. 您需要有权访问 MySQL 配置文件。
   位置可以从/etc/mysql/my.cnf到~/my.cnf（用户目录）。

2. 在部分中添加以下几行[mysqld]：

   collation_server = utf8_unicode_ci
   character_set_server=utf8
   

3. 重启服务器：

   service mysqld restart
   

回答by Tomalak

This is not a phpMyAdmin question.

这不是 phpMyAdmin 问题。

Collations are part of recent MySQL releases, you must set the default collation of the server (or at least of the database) to change that behaviour.

排序规则是最近 MySQL 版本的一部分，您必须设置服务器（或至少是数据库）的默认排序规则以更改该行为。

To convert already imported tables to UTF-8 you can do (in PHP):

要将已导入的表转换为 UTF-8，您可以执行以下操作（在 PHP 中）：

$dbname = 'my_databaseName';
mysql_connect('127.0.0.1', 'root', '');
mysql_query("ALTER DATABASE `$dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
$res = mysql_query("SHOW TABLES FROM `$dbname`");
while($row = mysql_fetch_row($res)) {
   $query = "ALTER TABLE {$dbname}.`{$row[0]}` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci";
   mysql_query($query);
   $query = "ALTER TABLE {$dbname}.`{$row[0]}` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";
   mysql_query($query);
}
echo 'all tables converted';

Code snippet taken from here.

取自此处的代码片段。

回答by Thor A. Pedersen

know this is an old post. But the way i changed the default charset through phpMyAdmin was:

知道这是一个旧帖子。但是我通过 phpMyAdmin 更改默认字符集的方式是：

phpMyadmin main page > Variables tab (Server variables and settings) > searched for "character" and changed all variables from "latin1" to "utf8". (on a MAMP installation with phpMyAdmin v. 3.5.7)

phpMyadmin 主页 > 变量选项卡（服务器变量和设置）> 搜索“字符”并将所有变量从“latin1”更改为“utf8”。（在带有 phpMyAdmin v. 3.5.7 的 MAMP 安装上）

And as the others said, this is the variables for MySQL and not some phpMyAdmin specific ones.

正如其他人所说，这是 MySQL 的变量，而不是某些特定于 phpMyAdmin 的变量。

回答by GSTAR

For utf8mb4, add/change the following in the [mysqld]section:

对于utf8mb4，在该[mysqld]部分添加/更改以下内容：

collation_server = utf8mb4_unicode_ci
character_set_server = utf8mb4

Then restart the mysqlservice (for Ubuntu the command is sudo service mysql restart)

然后重新启动mysql服务（对于 Ubuntu，命令是sudo service mysql restart）

回答by Yash

MySQL DB ? change CollationName of a Database|Table to utf8_general_ciinorder to support Unicode.

MySQL数据库？将数据库的排序规则名称|表更改utf8_general_ci为支持 Unicode。

Change Configuration settingsfile also

也更改配置设置文件

---

---

XAMPP: uncomment UTF 8 Settingsfrom the Configuration settings file ? D:\xampp\mysql\bin\my.ini

XAMPP：UTF 8 Settings从配置设置文件中取消注释？D:\xampp\mysql\bin\my.ini

## UTF 8 Settings
#init-connect=\'SET NAMES utf8\'
collation_server=utf8_unicode_ci
character_set_server=utf8
skip-character-set-client-handshake
character_sets-dir="D:/xampp/mysql/share/charsets"

---

---

For MySQL server to support UTF8 and the below line of code in file my.cnf

对于 MySQL 服务器支持 UTF8 和文件中的以下代码行 my.cnf

## UTF 8 Settings
collation_server=utf8_unicode_ci
character_set_server=utf8

---

---

@see

@看

• XAMPP - Apache Virtual Host Setting
• XAMPP security concept - Error 403,404

• XAMPP - Apache 虚拟主机设置
• XAMPP 安全概念 - 错误 403,404

回答by user251433

1. Insert this strings into my.ini(or my.cnf)

1. 将此字符串插入my.ini（或my.cnf）

[mysqld]
collation_server = utf8_unicode_ci
character_set_server=utf8

1. Add into config.inc.php

2. 添加到 config.inc.php

$cfg['DefaultConnectionCollation'] = 'utf8_general_ci';

1. You need to restart MySQLserver and remove cookies,data for domain where is located PhpMyAdmin

2. Reload page with PhpMyAmdinand at look the result

3. 您需要重新启动MySQL服务器并删除 cookie、PhpMyAdmin所在域的数据

4. 使用PhpMyAmdin重新加载页面并查看结果

回答by Nelson

I wanted to use utf8mb4 instead, and the configuration had to be the following:

我想改用 utf8mb4，配置必须如下：

collation_server = utf8mb4_general_ci
character_set_server=utf8mb4

the server would not start if the character_set was set to utf8

如果 character_set 设置为，服务器将不会启动 utf8