entityMap|blocks|key|jcrt|text|您需要单独转换每个表：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|br9g0|ALTER+TABLE+mytable+CONVERT+TO+CHARACTER+SET+utf8mb4|code-block|syntax|javascript|bjenn|(这也将转换列)，或使用以下命令导出数据库|f181|并使用以下命令将其重新导入|4ha29|..。^0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|N|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|O|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|P|9|@]|A|@]|B|$]]|$2|J|4|K|6|7|8|Q|9|@]|A|@]|B|$]]|$2|L|4|M|6|7|8|R|9|@]|A|@]|B|$]]]]

You need to either convert each table individually:

<pre><code>ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 
</code></pre>

(this will convert the columns just as well), or export the database with <code>latin1</code> and import it back with <code>utf8mb4</code>.

entityMap|blocks|key|aiogn|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data^0^^$0|$]|1|@$2|3|4|-4|5|6|7|B|8|@]|9|@]|A|$]]]]

you can set default collation at several levels:

<a href="http://dev.mysql.com/doc/refman/5.0/en/charset-syntax.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.0/en/charset-syntax.html</a>

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

entityMap|blocks|key|35ns1|text|您可以运行php脚本。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|a7lcr|$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!";
++++++++++++++++?>|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

You can run a php script.

<pre><code> &lt;?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 =&gt; $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!";
 ?&gt;
</code></pre>

entityMap|blocks|key|1esje|text|若要逐个更改表的排序规则，可以使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|7agts|ALTER+TABLE+mytable+CONVERT+TO+CHARACTER+SET+utf8|code-block|syntax|javascript|6865o|要为整个数据库设置默认排序规则，请执行以下操作：|95l6|ALTER+DATABASE++`databasename`+DEFAULT+CHARACTER+SET+utf8+COLLATE+utf8_bin|80tse|否则，|3m5o2|转到|93k8q|PhpMyAdmin->操作->排序。|2mc99|c8icu|在那里，您可以找到包含所有现有排序规则的选择框。这样您就可以在这里更改排序规则。因此，在这里，当您创建新列时，数据库表将遵循此排序规则。创建新列时不需要选择排序规则。^0|0|0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|U|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|V|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|W|9|@]|A|@]|B|$]]|$2|J|4|K|6|E|8|X|9|@]|A|@]|B|$F|G]]|$2|L|4|M|6|7|8|Y|9|@]|A|@]|B|$]]|$2|N|4|O|6|7|8|Z|9|@]|A|@]|B|$]]|$2|P|4|Q|6|7|8|10|9|@]|A|@]|B|$]]|$2|R|4|-4|6|7|8|11|9|@]|A|@]|B|$]]|$2|S|4|T|6|7|8|12|9|@]|A|@]|B|$]]]]

To change collation for tables individually you can use,

<pre><code>ALTER TABLE mytable CONVERT TO CHARACTER SET utf8
</code></pre>

To set default collation for the whole database,

<pre><code>ALTER DATABASE `databasename` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin
</code></pre>

or else, 

Goto PhpMyAdmin->Operations->Collation. 

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.

entityMap|blocks|key|5tv0g|text|您可以通过PHP脚本更改所有表的字符集和排序规则，如下所示。我喜欢hkasera的答案，但它的问题是查询在每个表上运行两次。除了使用MySqli而不是mysql和防止重复查询之外，这段代码几乎是相同的。如果我可以投票支持，我会投票支持hkasera的答案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|cunof|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();

?>|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

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.

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

$res-&gt;free();
$conn1-&gt;close();

?&gt;
</code></pre>

entityMap|blocks|key|9pje2|text|我在这里读到，你需要手动转换每个表，这不是真的。以下是如何使用存储过程完成此操作的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4hb8e|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,+'_%25');

--+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+;|code-block|syntax|javascript|bp09q|创建过程后，只需调用它：|3sgr0|CALL+changeCollation('utf8');|8p1tg|有关更多详细信息，请阅读以下内容|1eiv5|博客|ardn2|..。^0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|R|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|S|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|T|9|@]|A|@]|B|$]]|$2|J|4|K|6|E|8|U|9|@]|A|@]|B|$F|G]]|$2|L|4|M|6|7|8|V|9|@]|A|@]|B|$]]|$2|N|4|O|6|7|8|W|9|@]|A|@]|B|$]]|$2|P|4|Q|6|7|8|X|9|@]|A|@]|B|$]]]]

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:

<pre><code>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 ;
</code></pre>

After the procedure is created call it simply: 

<pre><code>CALL changeCollation('utf8');
</code></pre>

For more details read this <a href="http://blog.marit.ag/2015/06/09/mysql-change-collation-for-all-tables-in-your-database/" rel="nofollow noreferrer">blog</a>.

entityMap|blocks|key|d21t3|text|如果您运行phpMyAdmin+>>+select+database+>>+select+table+>>转到“表选项”部分中的“操作”选项卡>>，您可以从下拉列表>>中选择排序规则，一旦您按下屏幕顶部的{+go+}，您将看到一条消息：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3kqjp|您的SQL查询已成功执行|fs53s|832mu|和一个脚本|9f7dd|ALTER+TABLE+`tableName`+CONVERT+TO+CHARACTER+SET+utf8+COLLATE+utf8_general_ci|code-block|syntax|javascript|du53u|但它不会更改现有列的排序规则。为此，您可以使用此脚本(此脚本也来自phpMyAdmin)|407ao|ALTER+TABLE++`tableName`+CHANGE++`Name`++`Name`+VARCHAR(+255+)+CHARACTER+SET+utf8+COLLATE+utf8_general_ci+NOT+NULL^0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|Q|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|R|9|@]|A|@]|B|$]]|$2|E|4|-4|6|7|8|S|9|@]|A|@]|B|$]]|$2|F|4|G|6|7|8|T|9|@]|A|@]|B|$]]|$2|H|4|I|6|J|8|U|9|@]|A|@]|B|$K|L]]|$2|M|4|N|6|7|8|V|9|@]|A|@]|B|$]]|$2|O|4|P|6|J|8|W|9|@]|A|@]|B|$K|L]]]]

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: 

<blockquote>
 Your SQL query has been executed successfully 
</blockquote>

and a script 

<pre><code>ALTER TABLE `tableName` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci 
</code></pre>

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

<pre><code>ALTER TABLE `tableName` CHANGE `Name` `Name` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
</code></pre>

entityMap|blocks|key|e30ol|text|快捷方式-导出到SQL文件，使用搜索和替换来更改您需要更改的文本。创建新数据库，导入数据，然后将旧数据库和新数据库重命名为旧名称。|type|unstyled|depth|inlineStyleRanges|entityRanges|data^0^^$0|$]|1|@$2|3|4|5|6|7|8|C|9|@]|A|@]|B|$]]]]

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.

entityMap|blocks|key|4dmlj|text|以下查询将生成ALTER查询，该查询将所有表中所有适当列的归类更改为某种类型(utf8|type|unstyled|depth|inlineStyleRanges|entityRanges|data|e6s68|_|93u63|常规|b1g89|8odu8|在我下面的例子中)。|7g1ui|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');|code-block|syntax|javascript^0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|O|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|P|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|Q|9|@]|A|@]|B|$]]|$2|G|4|D|6|7|8|R|9|@]|A|@]|B|$]]|$2|H|4|I|6|7|8|S|9|@]|A|@]|B|$]]|$2|J|4|K|6|L|8|T|9|@]|A|@]|B|$M|N]]]]

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

<pre><code>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');
</code></pre>

entityMap|blocks|key|75i0c|text|您可以简单地将此代码添加到脚本文件中|type|unstyled|depth|inlineStyleRanges|entityRanges|data|e5f1n|//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!";|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

You can simple add this code to script file

<pre><code>//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 =&gt; $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!";
</code></pre>

entityMap|blocks|key|8n5sd|text|我在这里做贡献，正如操作员所问的那样：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|dfia6|bl8ks|如何更改数据库、表、列的排序规则？|6iqi8|ab5dr|1267v|选择的答案只是在表级别上说明它。|5la6b|在整个数据库范围内更改它：|b7r3o|ALTER+DATABASE++CHARACTER+SET+utf8mb4+COLLATE+utf8mb4_unicode_ci;|code-block|syntax|javascript|f3nd9|按表更改：|1fiaj|ALTER+TABLE++CONVERT+TO+CHARACTER+SET+utf8mb4+COLLATE+utf8mb4_unicode_ci;|95iv0|好的做法是在表级更改它，因为它也会针对列更改它。针对特定列的更改适用于任何特定情况。|2i3d7|更改特定列的排序规则：|2v2ad|ALTER+TABLE++MODIFY++VARCHAR(255)+CHARACTER+SET+utf8mb4+COLLATE+utf8mb4_unicode_ci;^0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|10|9|@]|A|@]|B|$]]|$2|C|4|-4|6|7|8|11|9|@]|A|@]|B|$]]|$2|D|4|E|6|7|8|12|9|@]|A|@]|B|$]]|$2|F|4|-4|6|7|8|13|9|@]|A|@]|B|$]]|$2|G|4|-4|6|7|8|14|9|@]|A|@]|B|$]]|$2|H|4|I|6|7|8|15|9|@]|A|@]|B|$]]|$2|J|4|K|6|7|8|16|9|@]|A|@]|B|$]]|$2|L|4|M|6|N|8|17|9|@]|A|@]|B|$O|P]]|$2|Q|4|R|6|7|8|18|9|@]|A|@]|B|$]]|$2|S|4|T|6|N|8|19|9|@]|A|@]|B|$O|P]]|$2|U|4|V|6|7|8|1A|9|@]|A|@]|B|$]]|$2|W|4|X|6|7|8|1B|9|@]|A|@]|B|$]]|$2|Y|4|Z|6|N|8|1C|9|@]|A|@]|B|$O|P]]]]

I am contributing here, as the OP asked:

<blockquote>
 How to change collation of database, table, column?
</blockquote>

The selected answer just states it on table level.

<hr>

Changing it database wide:

<pre><code>ALTER DATABASE &lt;database_name&gt; CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
</code></pre>

Changing it per table:

<pre><code>ALTER TABLE &lt;table_name&gt; CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
</code></pre>

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:

<pre><code>ALTER TABLE &lt;table_name&gt; MODIFY &lt;column_name&gt; VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
</code></pre>

entityMap|0|type|IMAGE|mutability|IMMUTABLE|data|imageUrl|https://ask.qcloudimg.com/http-save/actteam/6253b3c7119ec8e57247e50f8ac4ce7b.png|imageAlt|blocks|key|dj2su|text|我很惊讶地了解到，所以我不得不回到这里并报告，优秀的和维护良好的互连/it安全搜索和替换数据库脚本有一些选项，可以将表转换为utf8+/+unicode，甚至转换为innodb。这是一个通常用于将数据库驱动的网站(Wordpress、Drupal、Joomla等)从一个域迁移到另一个域的脚本。|unstyled|depth|inlineStyleRanges|entityRanges|75sfb|​|3obp8|9aphv|?|atomic|offset|length|3cvp3|3hc0i|ct17l|https://github.com/interconnectit/Search-Replace-DB|csp8e|https://interconnectit.com/products/search-and-replace-for-wordpress-databases/^0|0|0|0|0|1|0|0|0|0|0^^$0|$1|$2|3|4|5|6|$7|8|9|-4]]]|A|@$B|C|D|E|2|F|G|X|H|@]|I|@]|6|$]]|$B|J|D|K|2|F|G|Y|H|@]|I|@]|6|$]]|$B|L|D|-4|2|F|G|Z|H|@]|I|@]|6|$]]|$B|M|D|N|2|O|G|10|H|@]|I|@$P|11|Q|12|B|13]]|6|$]]|$B|R|D|-4|2|F|G|14|H|@]|I|@]|6|$]]|$B|S|D|K|2|F|G|15|H|@]|I|@]|6|$]]|$B|T|D|U|2|F|G|16|H|@]|I|@]|6|$]]|$B|V|D|W|2|F|G|17|H|@]|I|@]|6|$]]]]

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.

<a href="https://i.stack.imgur.com/Cgkwk.png" rel="nofollow"><img src="https://i.stack.imgur.com/Cgkwk.png" alt="interconnect script buttons"></a>

<ul>
<li><a href="https://github.com/interconnectit/Search-Replace-DB" rel="nofollow">https://github.com/interconnectit/Search-Replace-DB</a></li>
<li><a href="https://interconnectit.com/products/search-and-replace-for-wordpress-databases/" rel="nofollow">https://interconnectit.com/products/search-and-replace-for-wordpress-databases/</a></li>
</ul>

entityMap|blocks|key|f8i92|text|如果要更新架构上的默认字符集，请执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|eljj3|ALTER+SCHEMA+MYSCHEMA+DEFAULT+CHARACTER+SET+utf8+DEFAULT+COLLATE+utf8_unicode_ci;|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

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

<pre><code> ALTER SCHEMA MYSCHEMA DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;
</code></pre>

entityMap|blocks|key|5dobq|text|我使用了以下shell脚本。它接受数据库名称作为参数，并将所有表转换为另一个字符集和排序规则(由另一个参数或脚本中定义的默认值给定)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4beep|#!/bin/bash

#+mycollate.sh++[+]
#+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"+]+%7C%7C+exit+1
[+-n+"$CHARSET"+]+%7C%7C+CHARSET="utf8mb4"
[+-n+"$COLL"+]+%7C%7C+COLL="utf8mb4_general_ci"

echo+$DB
echo+"ALTER+DATABASE+$DB+CHARACTER+SET+$CHARSET+COLLATE+$COLL;"+%7C+mysql

echo+"USE+$DB;+SHOW+TABLES;"+%7C+mysql+-s+%7C+(
++++while+read+TABLE;+do
++++++++echo+$DB.$TABLE
++++++++echo+"ALTER+TABLE+$TABLE+CONVERT+TO+CHARACTER+SET+$CHARSET+COLLATE+$COLL;"+%7C+mysql+$DB
++++done
)|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

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

<pre><code>#!/bin/bash

# mycollate.sh &lt;database&gt; [&lt;charset&gt; &lt;collation&gt;]
# 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
)
</code></pre>

entityMap|blocks|key|3h023|text|只需运行此SQL即可一次转换所有数据库表。根据需要更改排序规则和databaseName。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|vuh8|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";|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

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

<pre><code>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";
</code></pre>

entityMap|blocks|key|5d6t7|text|我的解决方案是@Dzintars和@Quassnoi答案的组合。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|hkfm|SELECT+CONCAT("ALTER+TABLE+",+TABLE_SCHEMA,+'.',+TABLE_NAME,"+CONVERT+TO+CHARACTER+SET+utf8mb4+;")+AS++++ExecuteTheString
FROM+INFORMATION_SCHEMA.TABLES
WHERE+TABLE_SCHEMA=""
AND+TABLE_TYPE="BASE+TABLE";|code-block|syntax|javascript|8tdnn|通过使用|a2e3o|，这将生成一个脚本，该脚本将所有的表转换为您请求的编码。这也改变了|bnu5n|每列|akemc|啊！^0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|P|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|Q|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|R|9|@]|A|@]|B|$]]|$2|J|4|K|6|7|8|S|9|@]|A|@]|B|$]]|$2|L|4|M|6|7|8|T|9|@]|A|@]|B|$]]|$2|N|4|O|6|7|8|U|9|@]|A|@]|B|$]]]]

My solution is a combination of @Dzintars and @Quassnoi Answer.

<pre><code>SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 ;") AS ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="&lt;your-database&gt;"
AND TABLE_TYPE="BASE TABLE";
</code></pre>

By using <code>CONVERT TO</code>, this generates a scripts, which converts all the Tables of <code>&lt;your-database&gt;</code> to your requested encoding. This also changes the encoding of every column!

entityMap|blocks|key|emel9|text|我刚刚编写了一个bash脚本来查找给定数据库中的所有表并转换它们(及其列)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|896ei|这里提供了脚本：|9aik8|https://github.com/Juddling/mysql-charset^0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|G|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|H|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|I|9|@]|A|@]|B|$]]]]

I've just written a bash script to find all tables in a given database and covert them (and its columns).

Script is available here: <a href="https://github.com/Juddling/mysql-charset" rel="nofollow noreferrer">https://github.com/Juddling/mysql-charset</a>

entityMap|blocks|key|if7j|text|更好的变体，以生成SQL脚本的SQL请求。它不会破坏默认值/空值。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2hh5a|SELECT+concat
++++(
++++++++'ALTER+TABLE+',+
++++++++++++t1.TABLE_SCHEMA,+
++++++++++++'.',+
++++++++++++t1.table_name,+
++++++++++++'+MODIFY+',+
++++++++++++t1.column_name,+
++++++++++++'+',+
++++++++++++t1.column_type,
++++++++++++'+CHARACTER+SET+utf8+COLLATE+utf8_general_ci',
++++++++++++if(t1.is_nullable='YES',+'+NULL',+'+NOT+NULL'),
++++++++++++if(t1.column_default+is+not+null,+concat('+DEFAULT+\'',+t1.column_default,+'\''),+''),
++++++++++++';'
++++)
from+
++++information_schema.columns+t1
where+
++++t1.TABLE_SCHEMA+like+'your_table_here'+AND
++++t1.COLLATION_NAME+IS+NOT+NULL+AND
++++t1.COLLATION_NAME+NOT+IN+('utf8_general_ci');|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

Better variant to generate SQL script by SQL request. It will not ruin defaults/nulls.

<pre><code>SELECT concat
 (
 'ALTER TABLE ', 
 t1.TABLE_SCHEMA, 
 '.', 
 t1.table_name, 
 ' MODIFY ', 
 t1.column_name, 
 ' ', 
 t1.column_type,
 ' CHARACTER SET utf8 COLLATE utf8_general_ci',
 if(t1.is_nullable='YES', ' NULL', ' NOT NULL'),
 if(t1.column_default is not null, concat(' DEFAULT \'', t1.column_default, '\''), ''),
 ';'
 )
from 
 information_schema.columns t1
where 
 t1.TABLE_SCHEMA like 'your_table_here' AND
 t1.COLLATION_NAME IS NOT NULL AND
 t1.COLLATION_NAME NOT IN ('utf8_general_ci');
</code></pre>

entityMap|blocks|key|22ide|text|要一次更改数据库所有表中所有字段的排序规则，请执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|aa0eq|我只是通过前面提到的Php将表中的字段的另一个循环添加到解决方案中。这很有帮助，表中的所有字段也会被转换。|csnr0|$table)+{+++++++++++++++++++//+for+each+table

++++$sql+=+"ALTER+TABLE+$table+CONVERT+TO+CHARACTER+SET+utf8+COLLATE+utf8_general_ci";
++++echo+"\n".$sql;
++++mysql_query($sql);

++++$sql+=+"show+fields+in+".$table."+where+type+like+'varchar%25'+or+type+like+'char%25'+or+type='text'+or+type='mediumtext';";
++++$rs2=mysql_query($sql);
++++while(+$rw2+=+mysql_fetch_array($rs2)+){++++++++++++//+for+each+field+in+table

++++++++$sql+=+"ALTER+TABLE+`".$table."`+CHANGE+`".$rw2['Field']."`+`".$rw2['Field']."`+".$rw2['Type']."+CHARACTER+SET+utf8+COLLATE+utf8_general_ci+NOT+NULL;";
++++++++echo+"\n".$sql;
++++++++mysql_query($sql);

++++}+


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

?>}|code-block|syntax|javascript^0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|J|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|K|9|@]|A|@]|B|$]]|$2|E|4|F|6|G|8|L|9|@]|A|@]|B|$H|I]]]]

To change the collation of all fields in all tables of a database at once:

I was just adding another loop for the fields within the tables to the solution via Php before mentioned. This has helped, all fields in the tables are also converted.

<pre><code>&lt;?php
$con = mysql_connect('localhost','user','pw');
if(!$con) { echo "Cannot connect to the database ";die();}
mysql_select_db('database_name');
$result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {

foreach ($tables as $key =&gt; $table) { // for each table

 $sql = "ALTER TABLE $table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci";
 echo "\n".$sql;
 mysql_query($sql);

 $sql = "show fields in ".$table." where type like 'varchar%' or type like 'char%' or type='text' or type='mediumtext';";
 $rs2=mysql_query($sql);
 while( $rw2 = mysql_fetch_array($rs2) ){ // for each field in table

 $sql = "ALTER TABLE `".$table."` CHANGE `".$rw2['Field']."` `".$rw2['Field']."` ".$rw2['Type']." CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;";
 echo "\n".$sql;
 mysql_query($sql);

 } 


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

?&gt;}
</code></pre>

entityMap|blocks|key|1ktcn|text|注意，在更改了数据库/表/列的字符集之后，您可能需要实际转换现有数据(例如，如果您看到类似于“…？ˆ?ˆ?Š？”的内容)，如下所示：？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|bluj4|update+country+set+name+=+convert(cast(convert(name+using+latin1)+as+binary)+using+utf8),+cn_flag+=+convert(cast(convert(cn_flag+using+latin1)+as+binary)+using+utf8),+and+so+on..|code-block|syntax|javascript|4r6uc|而对于数据库、表和字段的转换，我建议|bfmhe|这个|brpfq|从这个帖子回答，它会生成一大组查询，你只需在粘贴时复制，在这里我还找不到一个自动的解决方案。另请注意，如果您将同一字段转换两次，您将得到不可恢复的问号：“？”。如果在转换字段/表之前转换数据，您也会看到此问号。^0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|N|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|O|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|P|9|@]|A|@]|B|$]]|$2|J|4|K|6|7|8|Q|9|@]|A|@]|B|$]]|$2|L|4|M|6|7|8|R|9|@]|A|@]|B|$]]]]

Note, after changing the charset for database/table/column, you might need to actually convert the existing data (if you see, for example, something like &quot;Ù…Ø·Ù„ÙˆØ¨ ØªÙˆØ±ÙŠØ¯ Ø¬Ù&quot;) with something like this:
<pre><code>update country set name = convert(cast(convert(name using latin1) as binary) using utf8), cn_flag = convert(cast(convert(cn_flag using latin1) as binary) using utf8), and so on..
</code></pre>
While for converting database, tables and fields, I would suggest <a href="https://stackoverflow.com/a/53509747/2090822">this</a> answer from this thread which would generate a big set of queries that you will just copy at paste, here I couldn't find an automatic solution yet.
Also be warned, if you will convert the same field twice you will get unrecoverable question marks: &quot;???&quot;. You will also get this question marks if you will convert data before converting fields/tables.

The database is <code>latin1_general_ci</code> now and I want to change collation to <code>utf8mb4_general_ci</code>.

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

How to change collation of database, table, column?

数据库

MariaDB

Drupal

MySQL

 数据库是 现在，我想将排序规则更改为 ..。 PhpMyAdmin中是否有任何设置可以更改数据库、表、列的排序规则？而不是一个接一个地改变？

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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