总结 1、create database时会自动生成一个文件db.opt，存放的数据库的默认字符集，show create database时显示数据库默认字符集即db.opt中字符集 2、这个文件丢失不影响数据库运行，该文件丢失之后新建表时，找不到数据库的默认字符集，就把character_set_server当成数据库的默认字符集，show create database时显示character_set_server字符集 mysql> show variables like 'character_set_server'; +----------------------+--------+ | Variable_name | Value | +----------------------+--------+ | character_set_server | latin1 | +----------------------+--------+ test1库没有指定字符集，使用character_set_server值 mysql> create database test1;
[root@mydb test1]# cat /var/lib/mysql/test1/db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci test2库指定了字符集utf16 mysql> create database test2 character set=utf16; [root@mydb test1]# cat /var/lib/mysql/test2/db.opt default-character-set=utf16 default-collation=utf16_general_ci test1库的默认字符集latin1，show create database显示默认字符集latin1 tab1表使用数据库默认字符集latin1 mysql> show create database test1; +----------+------------------------------------------------------------------+ | Database | Create Database | +----------+------------------------------------------------------------------+ | test1 | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+------------------------------------------------------------------+ mysql> create table test1.tab1 (hid int); test1库的没有了默认字符集，因为db.opt文件不存在了，show create database显示character_set_server字符集latin1 tab2表没有办法使用数据库默认字符集，使用character_set_server字符集latin1 [root@mydb test1]# mv db.opt db.opt20181015 [root@mydb test1]# service mysqld restart mysql> show create database test1; +----------+------------------------------------------------------------------+ | Database | Create Database | +----------+------------------------------------------------------------------+ | test1 | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+------------------------------------------------------------------+ mysql> create table test1.tab2 (hid int); test1库的没有了默认字符集，因为db.opt文件不存在了，show create database显示character_set_server字符集latin7 tab2表没有办法使用数据库默认字符集，使用character_set_server字符集latin7 [root@mydb test1]# vi /etc/my.cnf [mysqld] character_set_server=latin7 [root@mydb test1]# service mysqld restart mysql> show create database test1; +----------+------------------------------------------------------------------+ | Database | Create Database | +----------+------------------------------------------------------------------+ | test1 | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET latin7 */ | +----------+------------------------------------------------------------------+ mysql> create table test1.tab3 (hid int); test1库的默认字符集latin1，show create database显示默认字符集latin1 tab4表使用数据库默认字符集latin1 [root@mydb test1]# mv db.opt20181015 db.opt [root@mydb test1]# service mysqld restart mysql> show create database test1; +----------+------------------------------------------------------------------+ | Database | Create Database | +----------+------------------------------------------------------------------+ | test1 | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+------------------------------------------------------------------+ mysql> create table test1.tab4(hid int); tab1表使用数据库默认字符集latin1 mysql> show create table test1.tab1; +-------+-------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------+ | tab1 | CREATE TABLE `tab1` ( `hid` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+-------------------------------------------------------------------------------------------+ tab2表使用character_set_server字符集latin1 mysql> show create table test1.tab2; +-------+-------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------+ | tab2 | CREATE TABLE `tab2` ( `hid` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+-------------------------------------------------------------------------------------------+ tab3表使用character_set_server字符集latin7 mysql> show create table test1.tab3; +-------+-------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------+ | tab3 | CREATE TABLE `tab3` ( `hid` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin7 | +-------+-------------------------------------------------------------------------------------------+ tab4表使用数据库默认字符集latin1 mysql> show create table test1.tab4; +-------+-------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------+ | tab4 | CREATE TABLE `tab4` ( `hid` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+--------------------------------
