(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)
可见,如果表创建时,字段不指定字符集,字段的字符集就会继承表的字符集。