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

This article mainly explains "how to modify mysql character set and default storage engine". The explanation in this article is simple and clear and easy to learn and understand. please follow the editor's ideas to study and learn "how to modify mysql character set and default storage engine".

Modify the character set and default storage engine of mysql

1. Modify the character set of mysql

Mysql Library existing character set

Mysql > show variables like 'character%'

+-+

| | Variable_name | Value |

+-+

| | character_set_client | latin1 |

| | character_set_connection | latin1 |

| | character_set_database | latin1 |

| | character_set_filesystem | binary |

| | character_set_results | latin1 |

| | character_set_server | latin1 |

| | character_set_system | utf8 |

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

+-+

8 rows in set (0.00 sec) www.2cto.com

Mysql >

The character set of mysql is divided into several categories

a. Client character set: denoted by the system variable "character_set_client", notifies the server side of the encoding format of the sql statement submitted by the client.

b. Concatenation character set: indicated by the system variable "character_set_connectiont", the encoding format used by the server when translating sql statements

c. Result set character set: indicated by the system variable "character_set_results", the encoding format that the server converts the result set to before returning the result set

d. Stored character set: represented by the system variables "character_set_results" and "character_set_server", the data is encoded in the storage engine

To avoid garbled code, we need to modify these parameters uniformly. For example, I want to change the default character set to utf8, as shown below.

Add the following parameters under [client]

Default-character-set=utf8

Add the following parameters under [d]

Default-character-set=utf8

Then restart the mysql service and check the character set (to log in to the client again)

Www.2cto.com

Mysql > show variables like 'character%'

+-+

| | 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/ |

+-+

8 rows in set (0.01 sec)

Mysql >

Mysql > show variables like 'collation%'

+-+ +

| | Variable_name | Value |

+-+ +

| | collation_connection | utf8_general_ci |

| | collation_database | utf8_general_ci |

| | collation_server | utf8_general_ci |

+-+ +

3 rows in set (0.00 sec)

Mysql >

The character set of mysql is relatively fine, so you can use the default value or specify a value.

a. Database character set

Libraries that use the default character set

Mysql > create database db1

Query OK, 1 row affected (0.01sec)

Mysql > show create database db1

+-+

| | Database | Create Database |

+-+

| | db1 | CREATE DATABASE `db1` / *! 40100 DEFAULT CHARACTER SET utf8 * / | |

+-+

1 row in set (0.00 sec)

Specify the library of the character set

Mysql > create database db2 default character set latin1

Query OK, 1 row affected (0.01sec)

Mysql > show create database db2

+-+

| | Database | Create Database |

+-+

| | db2 | CREATE DATABASE `db2` / *! 40100 DEFAULT CHARACTER SET latin1 * / | |

+-+

1 row in set (0.00 sec)

Modify the character set of the library

Mysql > alter database db2 default character set utf8

Query OK, 1 row affected (0.00 sec)

Mysql > show create database db2

+-+

| | Database | Create Database www.2cto.com |

+-+

| | db2 | CREATE DATABASE `db2` / *! 40100 DEFAULT CHARACTER SET utf8 * / | |

+-+

1 row in set (0.00 sec)

b. Table character set

Mysql > use db2

Database changed

Tables that use the default library character set

Mysql > create table T1 (a varchar (10))

Query OK, 0 rows affected (0.01 sec)

Mysql > show create table T1

+-+ +

| | Table | Create Table |

+-+ +

| | T1 | CREATE TABLE `t1` (

`a`varchar (10) default NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-+ +

1 row in set (0.00 sec)

Creates a table of the specified character set

Mysql > create table T2 (a varchar (10)) default character set latin1

Query OK, 0 rows affected (0.01 sec)

ERROR:

No query specified

Mysql > show create table T2

+-+ +

| | Table | Create Table |

+-+ +

| | T2 | CREATE TABLE `t2` (

`a`varchar (10) default NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-+ +

1 row in set (0.00 sec)

Change the character set of a table

Mysql > alter table T2 default character set utf8

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > show create table T2

+-+ +

| | Table | Create Table www.2cto.com |

+-+ +

| | T2 | CREATE TABLE `t2` (

`a`varchar (10) character set latin1 default NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-+ +

1 row in set (0.00 sec)

Mysql >

As you can see from the above, when you modify the character set of a table, it will not affect the character set of existing columns, but the new column will inherit the character set of the table, as follows

Mysql > alter table T2 add a2 varchar (10)

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > show create table T2

+- -- +

| | Table | Create Table |

+- -- +

| | T2 | CREATE TABLE `t2` (

`a`varchar (10) character set latin1 default NULL

`a2` varchar (10) default NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+- -- +

1 row in set (0.01 sec)

Mysql >

If you want to change a character set that already exists for tables and character columns, use the following

Mysql > alter table T2 convert to character set latin1

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > show create table T2

+- -+

| | Table | Create Table |

+- -+

| | T2 | CREATE TABLE `t2` (

`a`varchar (10) default NULL

`a2` varchar (10) default NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+- -+

1 row in set (0.01 sec)

c. Field character set

Columns created using the default and specified character set

Mysql > create table T3 (A1 varchar (10), a2 varchar (10) character set latin1)

Query OK, 0 rows affected (0.04 sec) www.2cto.com

Mysql > show create table T3

+- -- +

| | Table | Create Table |

+- -- +

| | T3 | CREATE TABLE `t3` (

`a1` varchar (10) default NULL

`a2` varchar (10) character set latin1 default NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+- -- +

1 row in set (0.00 sec)

Mysql >

View the character set of a column

Mysql > show full columns from T3

+-+

| | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |

+-+

| | A1 | varchar (10) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |

| | a2 | varchar (10) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |

+-+

2 rows in set (0.01sec)

Modify the character set of a column

Mysql > alter table T3 change a2 a2 varchar (10) character set utf8

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > show full columns from T3

+-+

| | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |

+-+

| | A1 | varchar (10) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |

| | a2 | varchar (10) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |

+-+

2 rows in set (0.00 sec)

Mysql >

Conclusion: the character set inherited step by step from the character set of database, table and column.

View character set command

Mysql > show create table T4; / / display table character set

Mysql > show create database db2; / / display library character set

Mysql > show full columns from T4; / / Show column character set

Alter database db2 default character set utf8; / / change the character set of the library

Alter table T2 convert to character set latin1;// changes the existing column character set of the table

Alter table T2 default character set utf8; / / more capable of changing the default character set of the table

Alter table T3 change a2 a2 varchar (10) character set utf8; / / more capable of changing the character set of a field

Www.2cto.com

two。 Modify the default storage engine for mysql

Mysql > show variables like 'storage%'

+-+ +

| | Variable_name | Value |

+-+ +

| | storage_engine | MyISAM |

+-+ +

1 row in set (0.01 sec)

Mysql >

Add the following parameters under [mysqld]

Default-storage-engine=innodb

Restart the mysql service and view

Mysql > show variables like 'storage%'

+-+ +

| | Variable_name | Value |

+-+ +

| | storage_engine | InnoDB |

+-+ +

1 row in set (0.01 sec)

Mysql >

Create a table of default character sets

Mysql > create table T4 (A1 int)

Query OK, 0 rows affected (0.01 sec)

Mysql > show create table T4

+-+ +

| | Table | Create Table |

+-+ +

| | T4 | CREATE TABLE `t4` (

`a1` int (11) default NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-+ +

1 row in set (0.00 sec)

Change the default character set of the table

Mysql > alter table T4 engine myisam

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > show create table T4

+-+ + www.2cto.com

| | Table | Create Table |

+-+ +

| | T4 | CREATE TABLE `t4` (

`a1` int (11) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

+-+ +

1 row in set (0.00 sec)

Mysql >

Thank you for reading, the above is the content of "how to modify mysql character set and default storage engine". After the study of this article, I believe you have a deeper understanding of how to modify mysql character set and default storage engine. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.