总结
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` |
+----------+------------------------------------------------------------------+
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` |
+----------+------------------------------------------------------------------+
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` |
+----------+------------------------------------------------------------------+
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` |
+----------+------------------------------------------------------------------+
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 |
+-------+--------------------------------