天天看點
傳回

mysql concat 不同字元集_字元編碼 - 如何将整個MySQL資料庫字元集和排序規則轉換為UTF-8？...

字元編碼 - 如何将整個MySQL資料庫字元集和排序規則轉換為UTF-8？

如何将整個MySQL資料庫字元集轉換為UTF-8并将整理轉換為UTF-8？

17個解決方案

591 votes

使用utf8和utf8指令。

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

或者，如果您仍然使用不支援4位元組UTF-8的MySQL 5.5.2或更早版本，請使用utf8而不是utf8mb4：

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

BalusC answered 2019-01-04T16:25:02Z

116 votes

做個備份！

然後，您需要在資料庫上設定預設字元集。 這不會轉換現有表，它隻為新建立的表設定預設值。

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

然後，您将需要轉換所有現有表及其列的char集。 這假設您目前的資料實際上在目前字元集中。 如果您的列設定為一個字元集但您的資料确實存儲在另一個字元集中，則需要檢查MySQL手冊以了解如何處理此問題。

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

newspire answered 2019-01-04T16:25:48Z

70 votes

在指令行shell上

如果您是指令行shell，那麼您可以非常快速地執行此操作。 隻需填寫“dbname”：D

DB="dbname"

(

echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'

mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names \

| xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;'

) \

| mysql "$DB"

One-liner用于簡單複制/粘貼

DB="dbname"; ( echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'; mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) | mysql "$DB"

Jasny - Arnold Daniels answered 2019-01-04T16:26:26Z

65 votes

您可以使用以下指令建立sql以更新所有表：

SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET utf8 COLLATE utf8_general_ci; ",

"ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ")

AS alter_sql

FROM information_schema.TABLES

WHERE TABLE_SCHEMA = your_database_name;

捕獲輸出并運作它。

Arnold Daniels的回答更優雅。

sdfor answered 2019-01-04T16:27:05Z

14 votes

在繼續之前，請確定您：已完成完整的資料庫備份！

第1步：資料庫級别更改

識别資料庫的排序規則和字元集

SELECT CONCAT(

'ALTER TABLE ', table_name, ' CHARACTER SET utf8 COLLATE utf8_general_ci; ',

'ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ')

FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C

WHERE C.collation_name = T.table_collation

AND T.table_schema = 'your_database_name'

AND

(C.CHARACTER_SET_NAME != 'utf8'

OR

C.COLLATION_NAME not like 'utf8%')

修複資料庫的排序規則

SELECT CONCAT(

'ALTER TABLE ', table_name, ' CHARACTER SET utf8 COLLATE utf8_general_ci; ',

'ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ')

FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C

WHERE C.collation_name = T.table_collation

AND T.table_schema = 'your_database_name'

AND

(C.CHARACTER_SET_NAME != 'utf8'

OR

C.COLLATION_NAME not like 'utf8%')

第2步：表級更改

使用不正确的字元集或排序規則識别資料庫表

SELECT CONCAT(

'ALTER TABLE ', table_name, ' CHARACTER SET utf8 COLLATE utf8_general_ci; ',

'ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ')

FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C

WHERE C.collation_name = T.table_collation

AND T.table_schema = 'your_database_name'

AND

(C.CHARACTER_SET_NAME != 'utf8'

OR

C.COLLATION_NAME not like 'utf8%')

調整表列的排序規則和字元集

捕獲上層sql輸出并運作它。 (如下)

ALTER TABLE rma CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE rma_history CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_history CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE rma_products CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_products CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE rma_report_period CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_report_period CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE rma_reservation CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_reservation CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE rma_supplier_return CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_supplier_return CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE rma_supplier_return_history CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_supplier_return_history CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE rma_supplier_return_product CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_supplier_return_product CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

參考：[https://confluence.atlassian.com/display/CONFKB/How+to+Fix+the+Collation+and+Character+Set+of+a+MySQL+Database]

John Yin answered 2019-01-04T16:28:31Z

7 votes

使用HeidiSQL。 它是免費的，非常好的資料庫工具。

從工具菜單中，輸入批量表編輯器

選擇完整的資料庫或選擇要轉換的表格，

tick更改預設排序規則：utf8mb4_general_ci

tick轉換為charset：utf8

執行

這可以在幾秒鐘内将完整的資料庫從拉丁語轉換為utf8。

奇迹般有效 ：)

HeidiSQL預設連接配接為utf8，是以任何特殊字元現在應該被視為字元(æøå)，而不是在檢查表資料時編碼。

從拉丁語轉到utf8時真正的陷阱是確定pdo與utf8 charset連接配接。 如果沒有，你會将垃圾資料插入到utf8表中并在網頁上的所有地方出現問号，讓你認為表資料不是utf8 ...

Tom answered 2019-01-04T16:29:56Z

5 votes

受@sdfor評論的啟發，這是一個完成這項工作的bash腳本

#!/bin/bash

printf "### Converting MySQL character set ###\n\n"

printf "Enter the encoding you want to set: "

read -r CHARSET

# Get the MySQL username

printf "Enter mysql username: "

read -r USERNAME

# Get the MySQL password

printf "Enter mysql password for user %s:" "$USERNAME"

read -rs PASSWORD

DBLIST=( mydatabase1 mydatabase2 )

printf "\n"

for DB in "${DBLIST[@]}"

do

(

echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE `'"$CHARSET"'`;'

mysql "$DB" -u"$USERNAME" -p"$PASSWORD" -e "SHOW TABLES" --batch --skip-column-names \

| xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE `'"$CHARSET"'`;'

) \

| mysql "$DB" -u"$USERNAME" -p"$PASSWORD"

echo "$DB database done..."

done

echo "### DONE ###"

exit

Camoflame answered 2019-01-04T16:30:19Z

4 votes

如果資料不在同一個字元集中，您可以考慮[http://dev.mysql.com/doc/refman/5.0/en/charset-conversion.html]中的這個片段。

如果列具有非二進制資料類型(CHAR，VARCHAR，TEXT)，則為   内容應該在列字元集中編碼，而不是其他一些   字元集。 如果内容以不同的字元編碼   設定，您可以将列轉換為首先使用二進制資料類型，和   然後到具有所需字元集的非二進制列。

這是一個例子：

ALTER TABLE t1 CHANGE c1 c1 BLOB;

ALTER TABLE t1 CHANGE c1 c1 VARCHAR(100) CHARACTER SET utf8;

確定選擇正确的排序規則，否則您可能會遇到唯一的關鍵沖突。 例如在一些校對中，Éleanore和Eleanore可能被認為是相同的。

在旁邊：

我有一種情況，即某些字元在電子郵件中“破壞”，即使它們在資料庫中存儲為UTF-8。 如果您使用utf8資料發送電子郵件，您可能還希望将電子郵件轉換為以UTF8格式發送。

在PHPMailer中，隻需更新此行：public $CharSet = 'utf-8';

Frank Forte answered 2019-01-04T16:31:21Z

4 votes

對于具有大量表的資料庫，您可以使用簡單的PHP腳本使用以下内容更新資料庫的charset和所有表：

$conn = mysqli_connect($host, $username, $password, $database);

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

$alter_database_charset_sql = "ALTER DATABASE ".$database." CHARACTER SET utf8 COLLATE utf8_unicode_ci";

mysqli_query($conn, $alter_database_charset_sql);

$show_tables_result = mysqli_query($conn, "SHOW TABLES");

$tables = mysqli_fetch_all($show_tables_result);

foreach ($tables as $index => $table) {

$alter_table_sql = "ALTER TABLE ".$table[0]." CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";

$alter_table_result = mysqli_query($conn, $alter_table_sql);

echo "

";

var_dump($alter_table_result);

echo "

";

}

Dan Lucas answered 2019-01-04T16:31:43Z

3 votes

mysqldump -uusername -ppassword -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B dbname > dump.sql

cp dump.sql dump-fixed.sql

vim dump-fixed.sql

:%s/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/

:%s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/

:wq

mysql -uusername -ppassword < dump-fixed.sql

Soumik Guha answered 2019-01-04T16:31:59Z

1 votes

如果你不能讓你的表轉換或者你的表總是被設定為某個非utf8字元集，但你想要utf8，你最好的選擇可能是把它擦除并重新開始并明确指定：

create database database_name character set utf8;

user3013048 answered 2019-01-04T16:32:22Z

0 votes

唯一對我有用的解決方案：[http://docs.moodle.org/23/en/Converting_your_MySQL_database_to_UTF8]

轉換包含表的資料庫

mysqldump -uusername -ppassword -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B dbname > dump.sql

cp dump.sql dump-fixed.sql

vim dump-fixed.sql

:%s/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/

:%s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/

:wq

mysql -uusername -ppassword < dump-fixed.sql

utapyngo answered 2019-01-04T16:32:52Z

0 votes

alter table table_name charset ='utf8';

這是我能夠用于我的案例的簡單查詢，您可以根據您的要求更改table_name。

Aditya Seth answered 2019-01-04T16:33:22Z

0 votes

要将資料庫本身的字元集編碼更改為UTF-8，請在mysql＆gt;處鍵入以下指令： 提示。 将DBNAME替換為資料庫名稱：

ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci;

Nyein Aung answered 2019-01-04T16:33:44Z

0 votes

DELIMITER $$

CREATE PROCEDURE `databasename`.`update_char_set`()

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE t_sql VARCHAR(256);

DECLARE tableName VARCHAR(128);

DECLARE lists CURSOR FOR SELECT table_name FROM `information_schema`.`TABLES` WHERE table_schema = 'databasename';

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN lists;

FETCH lists INTO tableName;

REPEAT

SET @t_sql = CONCAT('ALTER TABLE ', tableName, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci');

PREPARE stmt FROM @t_sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

FETCH lists INTO tableName;

UNTIL done END REPEAT;

CLOSE lists;

END$$

DELIMITER ;

CALL databasename.update_char_set();

劍二十三 answered 2019-01-04T16:34:00Z

0 votes

最安全的方法是首先将列修改為二進制類型，然後使用所需的字元集将其修改回類型。

每個列類型都有各自的二進制類型，如下所示：

CHAR =＆gt;BINARY

TEXT =＆gt;BLOB

TINYTEXT =＆gt;TINYBLOB

MEDIUMTEXT =＆gt;MEDIUMBLOB

LONGTEXT =＆gt;LONGBLOB

VARCHAR =＆gt;VARBINARY

例如。：

ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] MODIFY [COLUMN_NAME] VARBINARY;

ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] MODIFY [COLUMN_NAME] VARCHAR(140) CHARACTER SET utf8mb4;

我嘗試了幾張latin1表，它保留了所有的變音符号。

您可以為執行此操作的所有列提取此查詢：

SELECT

CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.', TABLE_NAME,' MODIFY ', COLUMN_NAME,' VARBINARY;'),

CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.', TABLE_NAME,' MODIFY ', COLUMN_NAME,' ', COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')

FROM information_schema.columns

WHERE TABLE_SCHEMA IN ('[TABLE_SCHEMA]')

AND COLUMN_TYPE LIKE 'varchar%'

AND (COLLATION_NAME IS NOT NULL AND COLLATION_NAME NOT LIKE 'utf%');

在所有列上執行此操作後，您将在所有表上執行此操作：

ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

要為所有表生成此查詢，請使用以下查詢：

SELECT

CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_COLLATION NOT LIKE 'utf8%'

and TABLE_SCHEMA in ('[TABLE_SCHEMA]');

現在您修改了所有列和表，在資料庫上執行相同操作：

ALTER DATABASE [DATA_BASE_NAME] CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;

MalachiteBR answered 2019-01-04T16:35:56Z

-1 votes

您還可以使用資料庫工具Navicat，它更容易實作。

濕婆。

右鍵單擊您的資料庫＆amp; 選擇資料庫屬性＆amp; 在“下拉”中根據需要進行更改

mysql concat 不同字元集_字元編碼 - 如何将整個MySQL資料庫字元集和排序規則轉換為UTF-8？...

rvsiva17 answered 2019-01-04T16:36:32Z

mysql concat 不同字元集
上一篇: linux mysql字元集_Linux下MySQL 5.5/5.6的修改字元集編碼為UTF8的方法
下一篇: mysql中設定字元集語句_MYSQL字元集設定的方法詳解(終端的字元集)

繼續閱讀