~Onlooker
2011-05-24 19:12
采纳率: 0%
浏览 367

如何转换一个完整的 MySQL 数据库字符集和排序至 UTF-8?

How can I convert entire MySQL database character-set to UTF-8 and collation to UTF-8?

转载于:https://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

17条回答 默认 最新

  • larry*wei 2011-05-24 19:20
    已采纳

    Use the ALTER DATABASE and ALTER TABLE commands.

    ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    

    Or if you're still on MySQL 5.5.2 or older which didn't support 4-byte UTF-8, use utf8 instead of utf8mb4:

    ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
    ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • Lotus@ 2011-05-24 20:06
    1. Make a backup!

    2. Then you need to set the default char sets on the database. This does not convert existing tables, it only sets the default for newly created tables.

      ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;
      
    3. Then, you will need to convert the char set on all existing tables and their columns. This assumes that your current data is actually in the current char set. If your columns are set to one char set but your data is really stored in another then you will need to check the MySQL manual on how to handle this.

      ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
      
    评论
    解决 无用
    打赏 举报
  • ?yb? 2012-08-08 21:32

    On the commandline shell

    If you're one the commandline shell, you can do this very quickly. Just fill in "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 for simple copy/paste

    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"
    
    评论
    解决 无用
    打赏 举报
  • larry*wei 2013-10-10 17:02

    You can create the sql to update all tables with:

    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;
    

    Capture the output and run it.

    Arnold Daniels' answer above is more elegant.

    评论
    解决 无用
    打赏 举报
  • 10.24 2013-11-20 12:26

    If you cannot get your tables to convert or your table is always set to some non-utf8 character set, but you want utf8, your best bet might be to wipe it out and start over again and explicitly specify:

    create database database_name character set utf8;
    
    评论
    解决 无用
    打赏 举报
  • 狐狸.fox 2014-07-09 08:52

    The only solution that worked for me: http://docs.moodle.org/23/en/Converting_your_MySQL_database_to_UTF8

    Converting a database containing tables

    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
    
    评论
    解决 无用
    打赏 举报
  • 7*4 2014-12-04 16:26

    In case the data is not in the same character set you might consider this snippet from http://dev.mysql.com/doc/refman/5.0/en/charset-conversion.html

    If the column has a nonbinary data type (CHAR, VARCHAR, TEXT), its contents should be encoded in the column character set, not some other character set. If the contents are encoded in a different character set, you can convert the column to use a binary data type first, and then to a nonbinary column with the desired character set.

    Here is an example:

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

    Make sure to choose the right collation, or you might get unique key conflicts. e.g. Éleanore and Eleanore might be considered the same in some collations.

    Aside:

    I had a situation where certain characters "broke" in emails even though they were stored as UTF-8 in the database. If you are sending emails using utf8 data, you might want to also convert your emails to send in UTF8.

    In PHPMailer, just update this line: public $CharSet = 'utf-8';

    评论
    解决 无用
    打赏 举报
  • Memor.の 2014-12-30 13:11

    Use HeidiSQL. Its free and a very good db tool.

    From tools menu, enter Bulk table editor

    Select the complete database or pick tables to convert,

    • tick Change default collation: utf8mb4_general_ci
    • tick Convert to charset: utf8

    Execute

    This converts complete database from latin to utf8 in just a few seconds.

    Works like a charm :)

    HeidiSQL connects by default as utf8 so any special characters should now be seen as the character (æ ø å) and not as encoded when inspecting the table data.

    The real pitfall when moving from latin to utf8 is to make sure pdo connects with utf8 charset. If not you will get rubbish data inserted to the utf8 table and question marks all over the place on your web page, making you think the table data is not utf8...

    评论
    解决 无用
    打赏 举报
  • 零零乙 2015-01-05 02:21

    Before proceeding, ensure that you: Have completed a full database backup!

    Step 1: Database Level Changes

    • Identifying the Collation and Character set of your database

      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%');
      
    • Fixing the collation for the database

      ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
      

    Step 2: Table Level Changes

    • Identifying Database Tables with the incorrect character set or collation

      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%')
      
    • Adjusting table columns' collation and character set

    Capture upper sql output and run it. (like following)

    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; 
    

    refer to: https://confluence.atlassian.com/display/CONFKB/How+to+Fix+the+Collation+and+Character+Set+of+a+MySQL+Database

    评论
    解决 无用
    打赏 举报
  • ℡Wang Yan 2015-01-30 17:48

    For databases that have a high number of tables you can use a simple php script to update the charset of the database and all of the tables using the following:

    $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>";
    }
    
    评论
    解决 无用
    打赏 举报
  • 喵-见缝插针 2015-03-16 09:21

    alter table table_name charset = 'utf8';

    This is a simple query i was able to use for my case, you can change the table_name as per your requirement(s).

    评论
    解决 无用
    打赏 举报
  • ℡Wang Yan 2015-03-20 07:20

    Inspired by @sdfor comment, here is a bash script that does the job

    #!/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
    
    评论
    解决 无用
    打赏 举报
  • 谁还没个明天 2016-01-10 18:18
    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
    
    评论
    解决 无用
    打赏 举报
  • larry*wei 2016-01-25 07:09

    To change the character set encoding to UTF-8 for the database itself, type the following command at the mysql> prompt. Replace DBNAME with the database name:

    ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci;
    
    评论
    解决 无用
    打赏 举报
  • ℙℕℤℝ 2016-06-28 09:53

    You can also DB tool Navicat, which does it more easier.

    • Siva.

    Right Click Your Database & select DB Properties & Change as you desired in Drop Down

    enter image description here

    评论
    解决 无用
    打赏 举报
  • 零零乙 2018-06-06 16:54

    The safest way is to modify the columns first to a binary type and then modify it back to it type using the desired charset.

    Each column type have its respective binary type, as follows:

    1. CHAR => BINARY
    2. TEXT => BLOB
    3. TINYTEXT => TINYBLOB
    4. MEDIUMTEXT => MEDIUMBLOB
    5. LONGTEXT => LONGBLOB
    6. VARCHAR => VARBINARY

    Eg.:

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

    I tried in several latin1 tables and it kept all the diacritics.

    You can extract this query for all columns doing this:

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

    After you do this on all your columns then you do it on all tables:

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

    To generate this query for all your table, use the following query:

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

    And now that you modified all your columns and tables, do the same on the database:

    ALTER DATABASE [DATA_BASE_NAME] CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;
    
    评论
    解决 无用
    打赏 举报
  • lrony* 2018-06-21 04:47
    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();
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题