mysql convert(`id` using utf8)_如何设置mysql数据库为utf-8编码

创建一个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 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 --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 -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 p_w_uploads are stored in the database.

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

Warning: Always make backups.

Convert existing MySQL database from one charset encoding to anotherALTER 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.sqlImport database as utf8

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