Modify the character set and default storage engine of MySQL

Author: skate
Time: 2012/05/18

Modify the character set and default storage engine of MySQL

 

1. Modify the MySQL Character Set

MySQL library's 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)

Mysql>

MySQL character sets are classified into several categories
A. Client Character Set: it is represented by the system variable "character_set_client". It notifies the server, and the encoding format of the SQL statement submitted by the client.
B. Connected Character Set: it is represented by the system variable "character_set_connectiont". The encoding format used when the server translates SQL statements.
C. Result set Character Set: it is represented by the system variable "character_set_results". The encoding format converted from the result set before the server returns the result set.
D. Storage Character Set: it is represented by the system variables character_set_results and character_set_server. It is the encoding format of data in the storage engine.

 

To avoid garbled characters, we need to modify these parameters in a unified manner. For example, I want to change the default Character Set of the database to utf8, as shown below:

Add the following parameters under [client ]:
Default-character-set = utf8

Add the following parameters under [mysqld ]:
Default-character-set = utf8

Restart the MySQL service and view the character set (You need to log on to the client again)

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>

MySQL character set is relatively fine. You can use the default value or specify the value.

A. Database Character Set

Libraries that use the default Character Set
Mysql> Create Database db1;
Query OK, 1 row affected (0.01 Sec)

Mysql> show create database db1;
+ ---------- + -------------------------------------------------------------- +
| Database | create database |
+ ---------- + -------------------------------------------------------------- +
| Db1 | create database 'db1 '/*! 40100 default Character Set utf8 */|
+ ---------- + -------------------------------------------------------------- +
1 row in SET (0.00 Sec)

Library of the specified Character Set
Mysql> Create Database DB2 default Character Set Latin1;
Query OK, 1 row affected (0.01 Sec)

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 a 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 |
+ ---------- + -------------------------------------------------------------- +
| 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 using 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;
+ ------- + Response +
| Table | create table |
+ ------- + Response +
| T1 | create table 't1 '(
'A' varchar (10) default null
) Engine = InnoDB default charset = utf8 |
+ ------- + Response +
1 row in SET (0.00 Sec)

Create a table with 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;
+ ------- + Response +
| Table | create table |
+ ------- + Response +
| T2 | create table 't2 '(
'A' varchar (10) default null
) Engine = InnoDB default charset = Latin1 |
+ ------- + Response +
1 row in SET (0.00 Sec)

 

Changing 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;
+ ------- + Response +
| Table | create table |
+ ------- + Response +
| T2 | create table 't2 '(
'A' varchar (10) Character Set Latin1 default null
) Engine = InnoDB default charset = utf8 |
+ ------- + Response +
1 row in SET (0.00 Sec)

Mysql>

As shown in the preceding figure, When you modify the character set of a table, the character set of the existing column is not affected. However, when you add a new column, the new column inherits the character set of the table, as shown below:

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;
+ ------- + Certificate -------------- +
| Table | create table |
+ ------- + Certificate -------------- +
| T2 | create table 't2 '(
'A' varchar (10) Character Set Latin1 default null,
'A2 'varchar (10) default null
) Engine = InnoDB default charset = utf8 |
+ ------- + Certificate -------------- +
1 row in SET (0.01 Sec)

Mysql>

 

To change the character set of an existing table or character column, 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;
+ ------- + Response +
| Table | create table |
+ ------- + Response +
| T2 | create table 't2 '(
'A' varchar (10) default null,
'A2 'varchar (10) default null
) Engine = InnoDB default charset = Latin1 |
+ ------- + Response +
1 row in SET (0.01 Sec)

 

C. Field Character Set

 

Columns created using the default and specified character sets
Mysql> Create Table T3 (A1 varchar (10), A2 varchar (10) Character Set Latin1 );
Query OK, 0 rows affected (0.04 Sec)

Mysql> show create table T3;
+ ------- + Certificate --------------- +
| Table | create table |
+ ------- + Certificate --------------- +
| T3 | create table 't3 '(
'A1' varchar (10) default null,
'A2 'varchar (10) Character Set Latin1 default null
) Engine = InnoDB default charset = utf8 |
+ ------- + Certificate --------------- +
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.01 Sec)

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: character sets inherit from databases, tables, and columns step by step.

 

View character set commands
Mysql> show create table T4; // displays the table Character Set
Mysql> show create database DB2; // displays the library character set.
Mysql> show full columns from T4; // displays the column character set.

Alter database DB2 default Character Set utf8; // modify the character set of the database
Alter table T2 convert to Character Set Latin1; // you can change the character set of an existing column in the table.
Alter table T2 default Character Set utf8; // you can modify the default character set of a table.
Alter table T3 change A2 A2 varchar (10) Character Set utf8; // you can modify the character set of a field.

 

2. Modify the default storage engine of 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 with the default Character Set
Mysql> Create Table T4 (A1 INT );
Query OK, 0 rows affected (0.01 Sec)

Mysql> show create table T4;
+ ------- + Response +
| Table | create table |
+ ------- + Response +
| T4 | create table 't4 '(
'A1' int (11) default null
) Engine = InnoDB default charset = utf8 |
+ ------- + Response +
1 row in SET (0.00 Sec)

 

Change the default character set of a 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;
+ ------- + Response +
| Table | create table |
+ ------- + Response +
| T4 | create table 't4 '(
'A1' int (11) default null
) Engine = MyISAM default charset = utf8 |
+ ------- + Response +
1 row in SET (0.00 Sec)

Mysql>

 

 

 

------ End ------