Shulou(Shulou.com)05/31 Report--

How to carry out mysql character set adjustment example analysis, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Character set is a set of rules for symbols and coding. No matter in oracle database or in mysql database, there is a problem of character set selection. For the database, the character set is more important, because most of the data stored in the database are all kinds of characters, and the character set has an important impact on the storage, processing performance and data migration of the database.

If the character set is not selected correctly in the database creation phase, then the character set may need to be replaced at a later stage, and the replacement of the character set is a costly operation, and there is also a certain risk, so we recommend that at the beginning of the application, we should correctly select the appropriate character set according to the requirements and try to avoid unnecessary adjustments in the later stage.

When mysql compiles and installs, the method of specifying the character set:

. / configure-- with-charset=utf8

The mysql character set has four levels of default settings: server, database, table, and field. It is set in different places, and the function is also different.

1. The server character set setting is determined when the mysql service is started.

You can set it in my.cnf:

[mysql]

# default character set is utf8

Default-character-set=utf8

[mysqld]

# default character set is utf8

Default-character-set=utf8

# (set to use utf8 encoding when connecting to the mysql database to make the mysql database run for utf8)

Init_connect='SET NAMES utf8'

Or specify in the startup options:

Mysqld-default-character-set=utf8

If no specific server character set is specified, latin1 (an alias for ISO-8859-1) is used as the server character set by default. The above three settings only specify the character set without proofreading. We can use the show variables like 'char%'; command to query the characters of the current server.

Set and proofreading rules.

Mysql > show variables like 'char%'

+-+

| | Variable_name | Value |

+-+

| | character_set_client | utf8 |

| | character_set_connection | utf8 |

| | character_set_database | utf8 |

| | character_set_filesystem | binary |

| | character_set_results | utf8 |

| | character_set_server | utf8 |

| | character_set_system | utf8 |

| | character_sets_dir | / usr/share/mysql/charsets/ |

+-+

Note: if you add default-character-set=utf8, MYSQL starts the error message. Character_set_server=utf8 can be used instead of default-character-set=utf8, and it will start normally. This is due to a problem identified by different versions of MYSQL.

2. Database level

Specify the character set when creating the database

Mysql > CREATE DATABASE my_db default charset utf8 COLLATE utf8_general_ci

# notice the following sentence "COLLATE utf8_general_ci", which roughly means to sort according to the utf8 coding format when sorting.

If the database encoding is specified, the default character set for all data tables created under this database will be utf8

Modify the MYSQL database encoding. If the MYSQL database encoding is incorrect, you can execute the following command in MYSQL:

ALTER DATABASE my_db DEFAULT CHARACTER SET utf8

The above command sets the encoding of MYSQL's my_db database to utf8

3. Table level

Specify a character set when creating a table

Mysql > create table my_table (name varchar (20) not null default'') type=myisam default charset utf8

# this sentence is to create a table and specify the default character set as utf8

Modify the encoding of the MYSQL table:

ALTER TABLE my_table DEFAULT CHARACTER SET utf8

The above command is to change the encoding of a table my_table to utf8

4. Field level

Alter table test add column address varchar (110) after stu_id

Add a field address after stu_id

Alter table test add id int unsigned not Null auto_increment primary key

Modify the encoding of the field:

ALTER TABLE `test`CHANGE `name`name` VARCHAR (45) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

The above command is to change the field encoding of name in the test table of MYSQL database to utf8.

When inserting Chinese characters under the command line, the code is as follows:

Set names utf8;, sometimes this sentence is crucial!

Insert into charset values ('Wang Da')

Note: the alter modified method cannot update the character set of existing records, but only takes effect on newly created tables and records. For the adjustment of the existing recorded character set, the data needs to be exported first and then re-imported after appropriate adjustment before the encoding can be completely modified.

Export the imported character adjustment method:

Export table structure

Mysqldump-uroot-pmysql-- default-character-set=latin1-d my_db > createtab.sql

Manually modify the character set in the createtab.sql table structure definition to a new character set

1. Export all records

Mysqldump-uroot-pmysql-quick-no-create-info-extended-insert-default-character-set=latin1-host=localhost my_db > data.sql

2. Open data.sql and change set names latin1 to set names utf8

:% s/latin1/utf8/g

Full-text replacement

3. Create a new database with a new character set

Create database mydata default charset utf8

4. Create a table and execute createtab.sql

Mysql-uroot-pmysql mydata new.sql

Here, it is assumed that the original data is latin1 encoded by default.

Step 3: import

Modify old.sql, add a sql statement: "SET NAMES utf8;", save.

Mysql-hlocalhost-uroot my_db < new.sql

The great task has been completed!

Mysql collate rules:

* _ bin: indicates binary case sensitive collation, that is, it is case-sensitive

* _ cs: case sensitive collation, case sensitive

* _ ci: case insensitive collation, case-insensitive

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.