MySQL Character Set inheritance relationship Verification

MySQL Character Set inheritance relationship Verification

(1) When a database is created, a character set that is not specified will inherit the server character set.
Server characterset: utf8
Db characterset: latin1

Mysql> show global variables like 'character _ set_database ';
+ ------------------------ + -------- +
| Variable_name | Value |
+ ------------------------ + -------- +
| Character_set_database | latin1 |
+ ------------------------ + -------- +
1 row in set (0.00 sec)
 

Mysql> create database mytest2;
Query OK, 1 row affected (0.02 sec)
 

Mysql> show create database mytest2;
+ ---------- + ------------------------------------------------------------------ +
| Database | Create Database |
+ ---------- + ------------------------------------------------------------------ +
| Mytest2 | create database 'mytest2 '/*! 40100 default character set utf8 */|
+ ---------- + ------------------------------------------------------------------ +
1 row in set (0.00 sec)
 

It can be seen that when a database is created, if no character set is specified, it inherits the character set of the server and is not affected by the character_set_database value.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

(2) When a table is created without a specified character set, it inherits the database character set.
Mysql> create database mytest1 charset = latin1;
Query OK, 1 row affected (0.04 sec)
 

Mysql> show create database mytest1;
+ ---------- + -------------------------------------------------------------------- +
| Database | Create Database |
+ ---------- + -------------------------------------------------------------------- +
| Mytest1 | create database 'mytest1 '/*! 40100 default character set latin1 */| # the character set of the database is latin1.
+ ---------- + -------------------------------------------------------------------- +
1 row in set (0.00 sec)
 

Mysql> use mytest1
Database changed
Mysql> create table t (id int, name char (20); # No character set is specified when table t is created.
Query OK, 0 rows affected (0.13 sec)
 

Mysql> show create table t \ G
* *************************** 1. row ***************************
Table: t
Create Table: create table 'T '(
'Id' int (11) default null,
'Name' char (20) DEFAULT NULL
) ENGINE = InnoDB default charset = latin1
1 row in set (0.00 sec)
 

It can be seen that if no character set is specified during table creation, the character set of the database will be inherited.
 

 

(3) when creating a table, if the field does not specify a character set, the character set of the table is inherited by default.
Mysql> show create database mytest1;
+ ---------- + -------------------------------------------------------------------- +
| Database | Create Database |
+ ---------- + -------------------------------------------------------------------- +
| Mytest1 | create database 'mytest1 '/*! 40100 default character set latin1 */|
+ ---------- + -------------------------------------------------------------------- +
1 row in set (0.00 sec)
 

Mysql> create table t3 (id int, name char (20) character set utf8, text char (30 ));
Query OK, 0 rows affected (0.15 sec)
 

Mysql> show create table t3 \ G
* *************************** 1. row ***************************
Table: t3
Create Table: create table 't3 '(
'Id' int (11) default null,
'Name' char (20) character set utf8 default null,
'Text' char (30) DEFAULT NULL
) ENGINE = InnoDB default charset = latin1
1 row in set (0.00 sec)
 

It can be seen that if the character set is not specified for the field when the table is created, the character set of the field inherits the character set of the table.

This article permanently updates the link address: