mysql e r 继承关系_MySQL字符集继承关系验证

(1)创建数据库时不指定字符集会继承服务器字符集

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)

可见，创建数据库时，若没有指定字符集，那么会继承服务器的字符集，不受character_set_database值的影响。

--------------------------------------分割线 --------------------------------------

Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL http://www.linuxidc.com/Linux/2014-05/102351.htm

--------------------------------------分割线 --------------------------------------

(2)表创建不指定字符集时会继承数据库字符集

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 */ |  ## 这个可以看到，数据库的字符集为latin1

+----------+--------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> use mytest1

Database changed

mysql> create table t(id int,name char(20));  ## 创建表t时没有指定字符集

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)

可见，创建表时没有指定字符集，那么会继承数据库的字符集。

(3)创建表时，字段不指定字符集，则默认继承表的字符集

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)

可见，如果表创建时，字段不指定字符集，字段的字符集就会继承表的字符集。