mysql关于db.opt文件的总结
总结
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 |
+-------+--------------------------------
