如何设置mysql数据库为utf-8编码

原始出处：http://flandycheng.blog.51cto.com/855176/550616

原创作品，允许转载，转载时请务必以超链接形式标明文章  原始出处 、作者信息和本声明。否则将追究法律责任。 http://flandycheng.blog.51cto.com/855176/550616

创建一个UTF-8 编码的数据库
MySQL

    Create a UTF-8 database with binary UTF-8 collation.
    Binary UTF-8 provides case-sensitive collation.
    CREATE DATABASE confluence CHARACTER SET utf8 COLLATE utf8_bin;
    You will also need to set the Server Characterset to utf8. This can be done by adding the following in my.ini for Windows or my.cnf for other OS. It has to be declared in the Server section, which is the section after [mysqld]:
    [mysqld]
    default-character-set=utf8
    Use the status command to verify database character encoding information.

    Screenshot: Using the Status Command to Verify Database Character Encoding




    In some cases, the individual tables collation and character encoding may differ from the one that the database as a whole has been configured to use. Please use the command below to ensure all tables within your Confluence database are correctly configured to use UTF-8 character encoding and binary UTF-8 collation:
    use confluence;
    show table status;
    Check for the value listed under the Collation column, to ensure it has been set to utf8_bin (that is, case-sensitive) collation for all tables.
    If not, then this can be changed by the following command, executed for each table in the Confluence database:
    ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
    Please substitute the <tablename> above, with each table within the confluence database.

现有mysql数据库改成 UTF-8编码
MySQL database with existing data
    For an existing database
If you're using a existing database, confirm the Character Encoding by executing the query:
SHOW VARIABLES LIKE 'character%'; and SHOW VARIABLES LIKE 'collation%';.
The results should be UTF-8.
    Before proceeding with the following changes, please backup your database.
This example shows how to change your database from latin1 to utf8.

    Dump the database to a text file using mysqldump tool from the command-line :
    mysqldump -p --default_character-set=latin1 -u <username> --skip-set-charset confluence > confluence_database.sql
    copy confluence_database.sql to confluence_utf8.sql
    Open confluence_utf8.sql in a text editor and change all character sets from 'latin1' to 'utf8'
    replace method :
    (1)in vi or vim ::%s/CHARSET=latin1/CHARSET=utf8/gc    (2) sed -i 's/CHARSET=latin1/CHARSET=utf8/g' /path/to/confluence_utf8.sql 
    recode latin1..utf8 confluence_utf8.sql (the recode utility is described at http://directory.fsf.org/recode.html; it can actually be downloaded from http://recode.progiciels-bpi.ca/, and is available for Ubuntu via apt-get)

In MySQL:

    DROP DATABASE confluence;
    CREATE DATABASE confluence CHARACTER SET utf8 COLLATE utf8_bin;

Finally, reimport the UTF-8 text file:

    mysql -u <username> -p --default-character-set=utf8 --max_allowed_packet=64M confluence < /home/confluence/confluence_utf8.sql

To support large imports, the parameter '--max_allowed_packet=64M' used above sets the maximum size of an SQL statement to be very large. In some circumstances, you may need to increase it further, especially if attachments are stored in the database.

***************************************************************

Convert existing MySQL database from one charset encoding to another

Warning: Always make backups. 


Convert existing MySQL database from one charset encoding to another

ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Convert existing table from one charset encoding to another

ALTER TABLE db_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

The following commands seem useful (see Related )

Export your latin1 encoded database

mysqldump --user=username --password=password --default-character-set=latin1 --compatible=mysql40 dbname > dump.sql

Import database as utf8
mysql --user=username --password=password --default-character-set=utf8 dbname < dump.sql