mysql schema 字符集_MySQL字符集、information_schema元数据（八）

一、SQL字符集

它是一个系统支持的所有抽象字符的集合。字符是各种文字和符号的总称，包括各国家的文字、标点符号、图形符号、数字等

常用的字符集有：utf8、utf8mb4、utf8mb3(8.0)，现在推荐使用的是utf8mb4的字符集，支持更加丰富。它是数据库内部的存储格式

注意：要保证所有的字符集都一样，否则会乱码

1.1 CentOS7操作系统的语言

--查看现在使用的语言

# echo $LANG

en_US.UTF-8# locale

LANG=en_US.UTF-8LC_CTYPE="en_US.UTF-8"

LC_NUMERIC="en_US.UTF-8"

LC_TIME="en_US.UTF-8"

LC_COLLATE="en_US.UTF-8"

LC_MONETARY="en_US.UTF-8"

LC_MESSAGES="en_US.UTF-8"

LC_PAPER="en_US.UTF-8"

LC_NAME="en_US.UTF-8"

LC_ADDRESS="en_US.UTF-8"

LC_TELEPHONE="en_US.UTF-8"

LC_MEASUREMENT="en_US.UTF-8"

LC_IDENTIFICATION="en_US.UTF-8"

LC_ALL=

--临时修改

LANG="zh_CN.UTF-8" #修改为中文

LANG="en_US.UTF-8" #修改为英文--永久修改

cat /etc/locale.conf

LANG="en_US.UTF-8"

1.2 MySQL实例级别

A. 在编译的时候加上指定的参数

cmake .-DDEFAULT_CHARSET=utf8 \-DDEFAULT_COLLATION=utf8_general_ci \-DWITH_EXTRA_CHARSETS=all \

B. 非编译安装(5.6以前版本)修改my.cnf配置文件，默认是拉丁字符集

[mysqld]

character-set-server=utf8

1.3 数据库级别

CREATE DATABASE `wp` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE =utf8_general_ci;--CREATE DATABASE `wp` ： 　　　　代表的是创建数据库wp--DEFAULT CHARACTER SET utf8 ： 代表的是将该库的默认编码格式设置为utf8格式--COLLATE utf8_general_ci ：　　　 代表的是数据库校对规则，utf8_bin将字符串中的每一个字符用二进制数据存储，区分大小写。utf8_genera_ci不区分大小写，ci为case insensitive的缩写，即大小写不敏感。utf8_general_cs区分大小写，cs为case sensitive的缩写，即大小写敏感

1.4 MySQL客户端级别

--在mysql的连接中设置

mysql> setnames utf8;--修改my.cnf实现

[client]

default-character-set=utf8

1.5 表级别

CREATE TABLEtest (

idint(4) NOT NULLauto_increment,

namechar(20) NOT NULL,PRIMARY KEYid,

)ENGINE=INNODB auto_increment=13 DEFAULT CHARSET(=utf8);

1.6 生产环境更改数据库和表的字符集的方法

注意：更改字符集时，一定要保护由小往大改，后者必须是前者的严格超集。生产中不要随便修改。

ALTER DATABASE wp CHARACTER setutf8 COLLATE utf8_general_ci;alter table t1 CHARACTER SET utf8;

二、information_schema 获取元数据

查询information_schema数据库表，其中包含MySQL数据库服务器所管理的所有对象的相关数据。

mysql> useinformation_schema;--查看表结构

mysql> DESCTABLES;--一些常用列

TABLE_SCHEMA ：表所在的库

TABLE_NAME ：表的名称

ENGINE ：表的引擎

Table_rous ：统计表的数据行

Data_length+index_length ：统计空间使用情况

通过information schema常用的查询

--统计所有库的表的个数count()

SELECT table_schema,count(table_name) FROM TABLES GROUP BYtable_schema;--显示所有库下所有表的信息：

SELECT table_schema,table_name fromtables;--查看webdb库下视图的信息

select table_schema, table_name, table_type from tables where table_schema='webdb' and table_type='view';--统计数据库中所有innodb引擎的表

SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE FROM TABLES WHERE engine='innodb';--统计核心业务数据库所有表的行数，每天都要进行统计的信息。

SELECTTABLE_SCHEMA,TABLE_NAME,TABLE_ROWSFROM`TABLES`WHERE TABLE_SCHEMA IN ('mysql','webdb')ORDER BY TABLE_ROWS DESC;--统计每个数据库的数据量，MB为单位

SELECT TABLE_SCHEMA,sum('DATA_LENGTH'+'INDEX_LENGTH')/1024/1024

FROMTABLESGROUP BYTABLE_SCHEMA;--统计数据库的总占空间

SELECT sum(data_length+index_length)/1024/1024

FROMtables;--在/etc/my.cnf配置文件中增加一行，安全导出路径

# vim /etc/my.cnf

secure-file-priv=/backup#/etc/init.d/mysqld restart

# mkdir/backup# chown mysql.mysql/backup

--使用concat命令来批量生成分库分表备份的命令

SELECT CONCAT("mysqldump -uroot -p123456 ",TABLE_SCHEMA," ",TABLE_NAME,">/backup/",TABLE_SCHEMA,"_",TABLE_NAME,".sql") FROM TABLES INTO OUTFILE '/backup/bak.sh'

--指定webdb库进行分库分表的命令拼接

SELECT CONCAT("mysqldump -uroot -p123456 ",TABLE_SCHEMA," ",TABLE_NAME,">/backup/",TABLE_SCHEMA,"_",TABLE_NAME,".sql") FROM TABLES WHERE TABLE_SCHEMA='webdb' INTO OUTFILE '/backup/bak.sh'

--导出文件之后直接sh运行即可。