首页 文章

如何将整个MySQL数据库characterset和collation转换为UTF-8?

提问于
浏览
382

如何将整个MySQL数据库字符集转换为UTF-8并将整理转换为UTF-8?

17 回答

  • 0

    如果你不能让你的表转换或者你的表总是被设置为某个非utf8字符集,但你想要utf8,你最好的选择可能是把它擦除并重新开始并明确指定:

    create database database_name character set utf8;
    
  • 7

    使用ALTER DATABASEALTER TABLE命令 .

    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,请使用 utf8 而不是 utf8mb4

    ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
    ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
    
  • 71
    • Make a backup!

    • 然后,您需要在数据库上设置默认字符集 . 这不会转换现有表,它只为新创建的表设置默认值 .

    ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;
    
    • 然后,您需要转换所有现有表及其列的char集 . 这假设您当前的数据实际上在当前字符集中 . 如果您的列设置为一个字符集但您的数据确实存储在另一个字符集中,则需要检查MySQL manual如何处理此问题 .
    ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
    
  • 593

    在命令行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"
    

    用于简单复制/粘贴的单行程

    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"
    
  • 65

    您可以使用以下命令创建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的回答更优雅 .

  • 3

    在继续之前,请确保您:已完成完整的数据库备份!

    第1步:数据库级别更改

    • 标识数据库的排序规则和字符集
    SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM 
    information_schema.SCHEMATA S
    WHERE schema_name = 'your_database_name'
    AND
    (DEFAULT_CHARACTER_SET_NAME != 'utf8'
        OR
     DEFAULT_COLLATION_NAME not like 'utf8%');
    
    • 修复数据库的排序规则
    ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
    

    第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

  • 0

    使用HeidiSQL . 它是免费的,非常好的数据库工具 .

    从工具菜单中,输入批量表编辑器

    选择完整的数据库或选择要转换的表格,

    • tick更改默认排序规则:utf8mb4_general_ci

    • tick转换为charset:utf8

    执行

    这可以在几秒钟内将完整的数据库从拉丁语转换为utf8 .

    奇迹般有效 :)

    HeidiSQL默认连接为utf8,因此任何特殊字符现在应该被视为字符(æøå),而不是在检查表数据时编码 .

    从拉丁语转到utf8时真正的陷阱是确保pdo与utf8 charset连接 . 如果没有,你会将垃圾数据插入到utf8表中并在网页上的所有地方出现问号,让你认为表数据不是utf8 ...

  • 5

    受@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
    
  • 0

    如果数据不在同一个字符集中,您可以考虑来自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';

  • 4

    对于具有大量表的数据库,您可以使用简单的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 "<pre>";
      var_dump($alter_table_result);
      echo "</pre>";
    }
    
  • 1
    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
    
  • 0

    唯一对我有用的解决方案: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
    
  • -1

    alter table table_name charset ='utf8';

    这是我能够用于我的案例的简单查询,您可以根据您的要求更改table_name .

  • 14

    要将数据库本身的字符集编码更改为UTF-8,请在mysql>提示符下键入以下命令 . 将DBNAME替换为数据库名称:

    ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci;
    
  • 4
    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();
    
  • 117

    最安全的方法是首先将列修改为二进制类型,然后使用所需的字符集将其修改回类型 .

    每个列类型都有各自的二进制类型,如下所示:

    • CHAR => BINARY

    • TEXT => BLOB

    • TINYTEXT => TINYBLOB

    • MEDIUMTEXT => MEDIUMBLOB

    • LONGTEXT => LONGBLOB

    • VARCHAR => 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;
    
  • 0

    您还可以使用数据库工具Navicat,它更容易实现 .

    • 湿婆 .

    右键单击您的数据库,然后在下拉列表中根据需要选择数据库属性和更改

    enter image description here

相关问题