如何更改数据库、表、列的排序?

Now the database is latin1_general_ci and I want to change collation to utf8_general_ci. Is there any setting in PhpMyAdmin to change collation of database, table, column? Rather than changing one by one?

现在数据库是latin1_general_ci，我想将排序规则更改为utf8_general_ci。PhpMyAdmin中是否设置了数据库、表、列的排序?而不是一个一个地改变?

15 个解决方案

#1

196

You need to either convert each table individually:

您需要分别转换每个表:

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8

(this will convert the columns just as well), or export the database with latin1 and import it back with utf8.

(这也将转换列)，或者使用latin1导出数据库并使用utf8将其导入。

#2

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 utf8 COLLATE utf8_unicode_ci;

Changing it per table:

改变每个表:

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_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 utf8 COLLATE utf8_unicode_ci;

#3

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!";
                ?>

#4

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.

Goto 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.

在这里，您可以找到包含所有exsiting排序的选择框。这样你就可以改变排序了。因此，在创建新列时，数据库表将遵循这个排序。创建新列时不需要选择排序。

#5

you can set default collation at several levels:

您可以在几个级别设置默认的排序规则:

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)列

#6

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 >>选择数据库>>选择表>>进入"Operations"选项卡>>在" table options" section >>中你可以从下拉列表>0 >1中选择排序，当你在屏幕顶部按{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

#7

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');

#8

I was surprised to learn, and so I had to come back here and report, that the excellent and well maintained Interconnect/it SAFE SEARCH AND REPLACE ON DATABASE script has some options for converting tables to utf8 / unicode, and even to convert to innodb. It's a script commonly used to migrate a database driven website (Wordpress, Drupal, Joomla, etc) from one domain to another.

我很惊讶地发现，所以我不得不回到这里报告，优秀的、维护良好的互连/it安全搜索和替换在数据库脚本上有一些选项可以将表转换为utf8 / unicode，甚至转换为innodb。这个脚本通常用于将数据库驱动的网站(Wordpress、Drupal、Joomla等)从一个域迁移到另一个域。

https://github.com/interconnectit/Search-Replace-DB
https://github.com/interconnectit/Search-Replace-DB
https://interconnectit.com/products/search-and-replace-for-wordpress-databases/
https://interconnectit.com/products/search-and-replace-for-wordpress-databases/

#9

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脚本更改所有表的字符集和排序。我喜欢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();

?>

#10

I read it here, that you need to convert each table manually, it is not true. Here is a solution how to do it with a stored procedure:

我在这里读到，你需要手动转换每个表，这不是真的。这里有一个存储过程的解决方案:

DELIMITER $$

DROP PROCEDURE IF EXISTS changeCollation$$

-- character_set parameter could be 'utf8'
-- or 'latin1' or any other valid character set
CREATE PROCEDURE changeCollation(IN character_set VARCHAR(255))
BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_table_name varchar(255) DEFAULT "";
DECLARE v_message varchar(4000) DEFAULT "No records";

-- This will create a cursor that selects each table,
-- where the character set is not the one
-- that is defined in the parameter

DECLARE alter_cursor CURSOR FOR SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
AND COLLATION_NAME NOT LIKE CONCAT(character_set, '_%');

-- This handler will set the value v_finished to 1
-- if there are no more rows

DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;

OPEN alter_cursor;

-- Start a loop to fetch each rows from the cursor
get_table: LOOP

-- Fetch the table names one by one
FETCH alter_cursor INTO v_table_name;

-- If there is no more record, then we have to skip
-- the commands inside the loop
IF v_finished = 1 THEN
LEAVE get_table;
END IF;

IF v_table_name != '' THEN

IF v_message = 'No records' THEN
SET v_message = '';
END IF;

-- This technic makes the trick, it prepares a statement
-- that is based on the v_table_name parameter and it means
-- that this one is different by each iteration inside the loop

SET @s = CONCAT('ALTER TABLE ',v_table_name,
' CONVERT TO CHARACTER SET ', character_set);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET v_message = CONCAT('The table ', v_table_name ,
' was changed to the default collation of ', character_set,
'.\n', v_message);

SET v_table_name = '';

END IF;
-- Close the loop and the cursor
END LOOP get_table;
CLOSE alter_cursor;

-- Returns information about the altered tables or 'No records'
SELECT v_message;

END $$

DELIMITER ;

After the procedure is created call it simply:

程序创建后，简单地调用它:

CALL changeCollation('utf8’);

For more details read this blog.

更多细节请阅读本博客。

#11

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!";

#12

if you want to update the default charset on a schema:

如果您想更新模式上的默认字符集:

 ALTER SCHEMA MYSCHEMA DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;

#13

I used the following shell script. It takes database name as a parameter and converts all tables to another charset and collation (given by another parameters or default value defined in the script).

我使用了下面的shell脚本。它以数据库名称作为参数，并将所有表转换为另一个字符集和排序(由脚本中定义的另一个参数或默认值提供)。

#!/bin/bash

# mycollate.sh <database> [<charset> <collation>]
# changes MySQL/MariaDB charset and collation for one database - all tables and
# all columns in all tables

DB="$1"
CHARSET="$2"
COLL="$3"

[ -n "$DB" ] || exit 1
[ -n "$CHARSET" ] || CHARSET="utf8mb4"
[ -n "$COLL" ] || COLL="utf8mb4_general_ci"

echo $DB
echo "ALTER DATABASE $DB CHARACTER SET $CHARSET COLLATE $COLL;" | mysql

echo "USE $DB; SHOW TABLES;" | mysql -s | (
    while read TABLE; do
        echo $DB.$TABLE
        echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql $DB
    done
)

#14

Quick way - export to SQL file, use search and replace to change the text you need to change. Create new database, import the data and then rename the old database and the new one to the old name.

快速方法-导出到SQL文件，使用搜索和替换来更改需要更改的文本。创建新数据库，导入数据，然后将旧数据库和新数据库重命名为旧名称。

#15

Just run this SQL. Change your COLLATION to what you need and databaseName.

只是这个SQL运行。将排序规则更改为所需的内容和数据库名称。

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";

#1

196