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 |

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