MySQL 如何更改数据库、表、列的排序规则？

How to change collation of database, table, column?

提问by user158469

The database is latin1_general_cinow and I want to change collation to utf8mb4_general_ci.

数据库latin1_general_ci现在是，我想将排序规则更改为utf8mb4_general_ci.

Is there any setting in PhpMyAdmin to change collation of database, table, column? Rather than changing one by one?

PhpMyAdmin 中是否有任何设置可以更改数据库、表、列的排序规则？而不是一一改变？

回答by Quassnoi

You need to either convert each table individually:

您需要单独转换每个表：

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 

(this will convert the columns just as well), or export the database with latin1and import it back with utf8mb4.

（这也将转换列），或导出数据库latin1并使用utf8mb4.

回答by Nabeel Ahmed

I am contributing here, as the OP asked:

正如 OP 所问的那样，我在这里做出了贡献：

How to change collation of database, table, column?

如何更改数据库、表、列的排序规则？

The selected answer just states it on table level.

选定的答案只是在表级别上进行说明。

---

---

Changing it database wide:

在数据库范围内更改它：

ALTER DATABASE <database_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Changing it per table:

每个表更改它：

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Good practice is to change it at table level as it'll change it for columns as well. Changing for specific column is for any specific case.

好的做法是在表级别更改它，因为它也会更改列。更改特定列适用于任何特定情况。

Changing collation for a specific column:

更改特定列的排序规则：

ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

回答by hkasera

You can run a php script.

您可以运行一个 php 脚本。

               <?php
                   $con = mysql_connect('localhost','user','password');
                   if(!$con) { echo "Cannot connect to the database ";die();}
                   mysql_select_db('dbname');
                   $result=mysql_query('show tables');
                   while($tables = mysql_fetch_array($result)) {
                            foreach ($tables as $key => $value) {
                             mysql_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
                       }}
                   echo "The collation of your database has been successfully changed!";
                ?>

回答by jeeva

To change collation for tables individually you can use,

要单独更改表的排序规则，您可以使用，

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8

To set default collation for the whole database,

要为整个数据库设置默认排序规则，

ALTER DATABASE  `databasename` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin

or else,

要不然，

Goto PhpMyAdmin->Operations->Collation.

转到PhpMyAdmin-> 操作-> 整理。

There you an find the select box which contains all the exsiting collations. So that here you can change your collation. So here after database table will follows this collation while you are creating new column . No need of select collation while creating new columns.

在那里您可以找到包含所有现有排序规则的选择框。这样您就可以在此处更改排序规则。因此，在创建新列时，数据库表将遵循此排序规则。创建新列时无需选择排序规则。

回答by Parampal Pooni

The following query will generate ALTER queries that change the collation for all appropriate columns in all tables to a certain type (utf8_general_ci in my example below).

以下查询将生成 ALTER 查询，将所有表中所有适当列的排序规则更改为某种类型（在下面的示例中为 utf8_general_ci）。

SELECT concat
        (
            'ALTER TABLE ', 
                t1.TABLE_SCHEMA, 
                '.', 
                t1.table_name, 
                ' MODIFY ', 
                t1.column_name, 
                ' ', 
                t1.data_type, 
                '(' , 
                    CHARACTER_MAXIMUM_LENGTH, 
                ')', 
                ' CHARACTER SET utf8 COLLATE utf8_general_ci;'
        )
from 
    information_schema.columns t1
where 
    t1.TABLE_SCHEMA like 'you_db_name_goes_here' AND
    t1.COLLATION_NAME IS NOT NULL AND
    t1.COLLATION_NAME NOT IN ('utf8_general_ci');

回答by Yevgeniy Afanasyev

If you run phpMyAdmin >> select database >> select table >> go to "Operations" tab >> in "Table options" section >> you can pick Collation from the drop down list >> and once you press {Go} at the top of the screen you will see a message:

如果您运行 phpMyAdmin >> 选择数据库 >> 选择表 >> 转到“操作”选项卡 >> 在“表选项”部分 >> 您可以从下拉列表中选择排序规则 >> 并在按下 {Go} 后您将在屏幕顶部看到一条消息：

Your SQL query has been executed successfully

您的 SQL 查询已成功执行

and a script

和一个脚本

ALTER TABLE `tableName` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci 

But it will NOT change the collations of existing columns. To do so you can use this script (this one also came from phpMyAdmin)

但它不会改变现有列的排序规则。为此，您可以使用此脚本（此脚本也来自 phpMyAdmin）

ALTER TABLE  `tableName` CHANGE  `Name`  `Name` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL

回答by andersonbd1

you can set default collation at several levels:

您可以在多个级别设置默认排序规则：

http://dev.mysql.com/doc/refman/5.0/en/charset-syntax.html

http://dev.mysql.com/doc/refman/5.0/en/charset-syntax.html

1) client 2) server default 3) database default 4) table default 5) column

1) 客户端 2) 服务器默认 3) 数据库默认 4) 表默认 5) 列

回答by mtmehdi

You can change the CHARSET and COLLATION of all your tables through PHP script as follows. I like the answer of hkasera but the problem with it is that the query runs twice on each table. This code is almost the same except using MySqli instead of mysql and prevention of double querying. If I could vote up, I would have voted hkasera's answer up.

您可以通过 PHP 脚本更改所有表的 CHARSET 和 COLLATION，如下所示。我喜欢 hkasera 的答案，但问题是查询在每个表上运行两次。除了使用 MySqli 代替 mysql 和防止双重查询之外，这段代码几乎相同。如果我可以投票，我会投票支持 hkasera 的答案。

<?php
$conn1=new MySQLi("localhost","user","password","database");
if($conn1->connect_errno){
    echo mysqli_connect_error();
    exit;
}
$res=$conn1->query("show tables") or die($conn1->error);
while($tables=$res->fetch_array()){
    $conn1->query("ALTER TABLE $tables[0] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci") or die($conn1->error);
}
echo "The collation of your database has been successfully changed!";

$res->free();
$conn1->close();

?>

回答by Chandra Kumar

You can simple add this code to script file

您可以简单地将此代码添加到脚本文件中

//Database Connection
$host = 'localhost';
$db_name = 'your_database_name';
$db_user =  'your_database_user_name';
$db_pass = 'your_database_user_password';

$con = mysql_connect($host,$db_user,$db_pass);

if(!$con) { echo "Cannot connect to the database ";die();}

  mysql_select_db($db_name);

  $result=mysql_query('show tables');

  while($tables = mysql_fetch_array($result)) {
    foreach ($tables as $key => $value) {
    mysql_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
  }
}

echo "The collation of your database has been successfully changed!";

回答by Dzintars

Just run this SQL to convert all database tables at once. Change your COLLATION and databaseName to what you need.

只需运行此 SQL 即可一次转换所有数据库表。将您的 COLLATION 和 databaseName 更改为您需要的内容。

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," COLLATE utf8_general_ci;") AS    ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="databaseName"
AND TABLE_TYPE="BASE TABLE";