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 |

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