Modify mysql character set and default storage engine _ MySQL

Source: Internet
Author: User
Modify mysql character set and default storage engine bitsCN.com modify mysql character set and default storage engine 1. modify mysql character set mysql database current character set mysql> show variables like 'character % '; + bytes + | Variable_name | Value | + bytes + | character_set_client | latin1 | character_set_connection | latin1 | character_set_database | latin1 | character_set_filesystem | binary | Counter | latin1 | character_set_server | latin1 | character_set_system | utf8 | character_sets_dir |/usr/share/mysql/charsets/| + counter + 8 rows in set (0.00 sec) mysql> mysql character sets are classified into several types. client character set: it is represented by the system variable character_set_client. it notifies the server that the encoding format of the SQL statement submitted by the client is B. character Set connection: the character set is represented by the system variable "character_set_connectiont". the encoding format C. result set character set: using the system variable "ch Aracter_set_results "indicates that the server converts the result set to the encoding format D before returning the result set. storage character set: it is represented by the system variables "character_set_results" and "character_set_server". it is the data encoding format 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, add the following parameter default-character-set = utf8 under [mysqld] as shown in the following figure. add the following parameter default-character-set = utf8 under [mysqld] and then restart the mysql service, check the character set (to log on to the client again) mysql> show variables like 'character % '; + ------------------------ + -------------------------- + | Variable_name | Value | + character + | character_set_client | utf8 | character_set_connection | utf8 | character_set_database | utf8 | character | binary | character | utf8 | character_set_server | utf8 | utf8 | character_sets_dir |/usr/share/mysql/charsets/| + ------------------------ + ------------------- --------- + 8 rows in set (0.01 sec) mysql> show variables like 'colation % '; + rows + --------------- + | Variable_name | Value | + rows + --------------- + | collation_connection | rows | collation_database | rows | collation_server | rows | + rows + 3 rows in set (0.00 sec) mysql> mysql character set is fine To use the default value, you can also specify the value. database character sets use the default character set Library mysql> create database db1; Query OK, 1 row affected (0.01 sec) mysql> show create database db1; + ---------- + databases + | Database | Create Database | + ---------- + ------------------------------------------------------------ + | db1 | create database 'db1 '/*! 40100 default character set utf8 */| + ---------- + keys + 1 row in set (0.00 sec) database 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 */| + ---------- + character + 1 row in set (0.00 sec) modify the CHARACTER set of the database 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; + ------- + tables + | Table | Create Table | + ------- + tables + | t1 | create table 'T1' ('A' varchar (10) default NULL) ENGINE = InnoDB default charset = utf8 | + ------- + rows + 1 row in set (0.00 sec) create a table mysql> create table t2 (a varchar (10) default character set latin1; Query OK, 0 rows affected (0.01 sec) ERROR: no query specifiedmysql> show create table t2; + ------- + partition + | Table | Create Table | + ------- + partition + | t2 | create table 'T2' ('A' varchar (10) default NULL) ENGINE = InnoDB default charset = latin1 | + ------- + rows + 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; + ------- + creator + | Table | Create Table | + ------- + creator + | t2 | create table 'T2' ('A' varchar (10) character set latin1 default NULL) ENGINE = InnoDB default charset = utf8 | + ------- + rows + 1 row in set (0.00 sec) mysql> as shown above, when you modify the character set of the table, the character set of an existing column is not affected, but the new column inherits the character set of the table. for example, 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; + ------- + tables -------------- + | Table | Create Table | + ------- + tables ---------------- + | t2 | create table 'T2' ('A' varchar (10) character set latin1 default NULL, 'A2 'varchar (10) default NULL) ENGINE = InnoDB default charset = utf8 | + ------- + bytes ---------------- + 1 row in set (0.01 sec) mysql> to change the character set of an existing table and 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; + ------- + partition + | Table | Create Table | + ------- + partition + | t2 | create table 'T2' ('A' varchar (10) default NULL, 'A2 'varchar (10) default NULL) ENGINE = InnoDB default charset = latin1 | + ------- + rows + 1 row in set (0.01 sec) C. the field character set uses the default and specified character set to create columns 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; + ------- + hour --------------- + | Table | Create Table | + ------- + hour ----------------- + | t3 | create table 't3 '('A1' varchar (10) default NULL, 'A2 'varchar (10) character set latin1 default NULL) ENGINE = InnoDB default charset = utf8 | + ------- + response ----------------- + 1 row in set (0.00 sec) mysql> view the column character set mysql> show full columns from t3; + ------- + ------------- + ----------------- + ------ + ----- + --------- + ------- + hour + --------- + | 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 column character set 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; + ------- + ------------- + ----------------- + ------ + ----- + --------- + ------- + hour + --------- + | 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 are inherited step by step from the character sets of databases, tables, and columns. View character set commands mysql> show create table t4; // display the table character set mysql> show create database db2; // display the library character set mysql> show full columns from t4; // display 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; // modify the character set of the existing columns of the table alter table t2 default character set utf8; // modify the default character set of the table. alter table t3 change a2 a2 varchar (10) character set utf8; // modify the character set of the field. modify the default mysql storage engine mysql> show variables like 'Storage % '; + ---------------- + -------- + | Variable_name | Value | + ------------------ + -------- + | storage_engine | MyISAM | + ---------------- + -------- + 1 row in set (0.01 sec) mysql> under [mysqld], add the following parameter default-storage-engine = innodb to restart the mysql service. View mysql> show variables like 'Storage % '; + ---------------- + -------- + | Variable_name | Value | + ------------------ + -------- + | storage_engine | InnoDB | + ---------------- + -------- + 1 row in set (0.01 sec) mysql> create a default character set table mysql> create table t4 (a1 int); Query OK, 0 rows affected (0.01 sec) mysql> show create table t4; + ------- + keys + | Table | Create Table | + ------- + keys + | t4 | create table 'T4 '('A1' int (11) default NULL) ENGINE = InnoDB default charset = utf8 | + ------- + rows + 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; + ------- + keys + | Table | Create Table | + ------- + keys + | t4 | create table 'T4 '('A1' int (11) default NULL) ENGINE = MyISAM default charset = utf8 | + ------- + rows + 1 row in set (0.00 sec) mysql> by wyzxgbitsCN.com
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.