五、MySQL常用功能实践

2017-08-12 本文已影响186人 BruceLiu1

图片来自网络

文/Bruce.Liu1

文章大纲

字符集
1.1. 字符集概述
1.2. 常见字符集
1.3. 分析乱码过程

MySQL字符集
2.1. MySQL常用字符集
2.2. MySQL字符集特性
2.3. MySQL字符集设置
2.4. MySQL校验规则
2.5. 字符集建议
2.6. 字符集最佳实践

数据类型
3.1. Integer类型
3.2. Number类型
3.3. String类型
3.4. 集合类型
3.5. 日期类型

权限管理
4.1. MySQL权限概念
4.2. 权限介绍
4.3. 用户管理
4.4. proxy user

1.字符集

1.1.字符集概述

为什么要有字符集?我们在计算机屏幕上看到的是实体化的文字，而在计算机存储介质中存放的实际是二进制的比特流。那么在这两者之间的转换规则就需要一个统一的标准，否则把我们的U盘插到老板的电脑上，文档就乱码了；小伙伴QQ上传过来的文件，在我们本地打开又乱码了。于是为了实现转换标准，各种字符集标准就出现了。简单的说字符集就规定了某个文字对应的二进制数字存放方式（编码）和某串二进制数值代表了哪个文字（解码）的转换关系。

字符集只是一个规则集合的名字，对应到真实生活中，字符集就是对某种语言的称呼。例如：英语，汉语，日语。对于一个字符集来说要正确编码转码一个字符需要三个关键元素：字库表（character repertoire）、编码字符集（coded character set）、字符编码（character encoding form）。其中字库表是一个相当于所有可读或者可显示字符的数据库，字库表决定了整个字符集能够展现表示的所有字符的范围。编码字符集，即用一个编码值 code point来表示一个字符在字库中的位置。字符编码，将编码字符集和实际存储数值之间的转换关系。一般来说都会直接将code point的值作为编码后的值直接存储。例如在ASCII中A在表中排第65位，而编码后A的数值是0100 0001也即十进制的65的二进制转换结果。

1.2.常见字符集

ASCII

(American Standard Code for Information Interchange，美国标准信息交换代码）是基于拉丁字母的一套电脑编码系统，主要用于显示现代英语和其他西欧语言，其最多只能用 8 位来表示（一个字节），即：2**8 = 256，所以，ASCII码最多只能表示 256 个符号。

计算机是西方国家的产物，因此计算机语言很自然的就是英语，就像现在的开发语言也是一样也都是英语进行编码的，也许是设计者没有想到计算机能够快速流行开来；或者是开发设计者没有想到每个国家的语音、文字环境的问题。Whatever，随着计算机的快速发展，计算机科学家们很快的意识到ASCII编码格式无法解决全球各个国家的语言文字问题，他们必须要设计一个标准，能够涵盖任何的字符编码。

Unicode

Unicode（统一码、万国码、单一码）是一种在计算机上使用的字符编码。Unicode 是为了解决传统的字符编码方案的局限而产生的，它为每种语言中的每个字符设定了统一并且唯一的二进制编码，规定虽有的字符和符号最少由 16 位来表示（2个字节），即：2 **16 = 65536，
注：此处说的的是最少2个字节，可能更多

Unicode编码标准的出现极大的解决了，编码统一的问题，同时又伴随着另外一个问题，在ASCII中一个字母就是用一个字节来表示就行，但在Unicode中却必须要用2个字节来表示，这无疑在当时计算机硬件发展不是特别发达的情况下为计算机带来更多的额外的负担，因此科学家们又在着手解决该问题，如何在统一编码标准的情况下，又能让表示的编码达到最小化呢?

UTF-8

UTF-8编码为变长编码。最小编码单位（code unit）为一个字节。一个字节的前1-3个bit为描述性部分，后面为实际序号部分。它是对Unicode编码的压缩和优化，他不再使用最少使用2个字节，而是将所有的字符和符号进行分类：ascii码中的内容用1个字节保存、欧洲的字符用2个字节保存，东亚的字符用3个字节保存。

Unicode就是上文中提到的编码字符集，而UTF-8就是字符编码，即Unicode规则字库的一种实现形式(也可以叫做Unicode的扩展子集)。为解决Unicode字母型数据造成浪费的问题，utf-8中字母用一个字节表示，中文等用三个字节表示(灵活可变的长度应对不用的类型的数据)

1.3.分析乱码过程

为什么会出现乱码?
简单的说乱码的出现是因为：编码和解码时用了不同或者不兼容的字符集。对应到真实生活中，就好比是一个英国人为了表示祝福在纸上写了bless（编码过程）。而一个法国人拿到了这张纸，由于在法语中bless表示受伤的意思，所以认为他想表达的是受伤（解码过程）。这个就是一个现实生活中的乱码情况。在计算机科学中一样，一个用UTF-8编码后的字符，用GBK去解码。由于两个字符集的字库表不一样，同一个汉字在两个字符表的位置也不同，最终就会出现乱码。

乱码的过程：

编码
假设我们在页面上看到浜屽搱这样的乱码，而又得知我们的浏览器当前使用GBK编码。那么第一步我们就能先通过GBK把乱码编码成二进制表达式。当然查表编码效率很低，我们也可以用以下SQL语句直接通过MySQL客户端做编码工作：

mysql> select hex(convert('浜屽搱' using gbk));
+-------------------------------------+
| hex(convert('浜屽搱' using gbk))    |
+-------------------------------------+
| E4BA8CE59388                        |
+-------------------------------------+
1 row in set (0.01 sec)

识别
现在我们得到了解码后的二进制字符串E4BA8CE59388。然后我们将它按字节拆开。就不难发现这6个字节的数据符合UTF-8编码规则。如果整个数据流都符合这个规则的话，我们就能大胆假设乱码之前的编码字符集是UTF-8

图片来自原创

解码
然后我们就能拿着E4BA8CE59388用UTF-8解码，查看乱码前的文字了。当然我们可以不查表直接通过SQL获得结果：

mysql> select convert(0xE4BA8CE59388 using utf8);
+------------------------------------+
| convert(0xE4BA8CE59388 using utf8) |
+------------------------------------+
| 二哈                               |
+------------------------------------+
1 row in set (0.00 sec)

2.MySQL字符集

2.1.MySQL常用字符集

gbk/gb2312

采用双字节字符集，不论中、英文字符均使用双字符来表示，为了区分中文，将其最高位都设定成1
gb2312是gbk的子集，gbk是gb18030的子集，gb2312仅能存储简体中文字符
gbk包括中日韩字符的大字符集
通常使用bgk字符集足够
国际通用性比utf8差，不过utf8占用的数据库比gbk大(utf8是三字节字符集)

utf8/utf8mb4

UTF全称(Unicode Transformation Format)，是Unicode的一种存储方式，可变长度字符编码，又称万国码
数据库字符集尽量使用utf8(包括 connect、result、及最终html页面都必须要求一致为ut8)
UTF8使用可变长度字节来存储 Unicode字符，例如 ASCII字母继续使用1个字节存储，重音文字、希腊字母、或西里尔字母等使用2个字节来存储，而常用的汉字就要用3个字节
utf8mb4一个字符最多能存4字节，所以能支持更多的字符集；项目中常用utf8mb4存放emoji表情

latin1

是8bit (1 bytes)字符集，但不能覆盖亚洲、非洲语言
unidoce是latin1的扩展，增加了亚洲、非洲常规语言支持，但仍不支持全部语言，且ASCII用unidoce来表示效率不高(小字符集转换成大字符集,往往便随的就是字符的丢失)
utf8是unicode的扩展
gbk、gb2312等字符集与utf8之间都必须通过Unicode编码才能相互转换

讨论：char(30)，在不同的字符集中最多能存放多少个字母、汉字，以及占用空间情况

2.2.MySQL字符集特性

图片来自原创

MySQL字符集范围

服务器层(server) > 数据库成(database) > 数据表(table) > 字段(column) > 连接(connection) | 结果集(result)

MySQL字符集优先级

连接(connection) | 结果集(result) > 字段(column) > 数据表(table) > 数据库成(database) > 服务器层(server)

MySQL字符集继承关系

server：server > database > tables > column
client：connection > result

character_set_system是什么鬼?

The character set used by the server for storing identifiers. The value is always utf8.(只读参数,表示元数据的编码格式)

mysql> show global variables like '%char%';
+--------------------------+-------------------------------------+
| Variable_name            | Value                               |
+--------------------------+-------------------------------------+
| character_set_client     | utf8                                |
| character_set_connection | utf8                                |
| character_set_database   | utf8                                |
| character_set_filesystem | binary                              |
| character_set_results    | utf8                                |
| character_set_server     | utf8                                |
| character_set_system     | utf8                                |
| character_sets_dir       | /usr/share/percona-server/charsets/ |
+--------------------------+-------------------------------------+

2.3.MySQL字符集配置

查看MySQL支持的字符集

default collation是什么，我们后面展开说明。

mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
...... 省略 ......

指定数据库字符集

mysql> create database db_gbk character set gbk;

指定表字和列字符集

mysql> use db_gbk
mysql> create table t_utf8 (col varchar(10) character set latin1) character set utf8;
Query OK, 0 rows affected (0.01 sec)

查看字符集

mysql> show create database db_gbk;
mysql> show create table db_gbk.t_utf8;

2.4.MySQL校验规则

刚才我们看到支持字符集后面有一个default collation这是什么呢？我们先做一个实验

mysql> create table t_collation (emp_name varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_collation values ('Tom');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_collation values ('tom');
Query OK, 1 row affected (0.00 sec)

默认MySQL查询是也是不区分大小写，而MySQL返回的值确实比较宽泛。这就是默认校验规则是不区分大小写造成的

mysql> select * from t_collation where emp_name = 'tom';
+----------+
| emp_name |
+----------+
| Tom      |
| tom      |
+----------+
2 rows in set (0.00 sec)

修改校验规则

mysql> show global variables like '%collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

mysql> alter table t_collation change emp_name emp_name varchar(10) DEFAULT NULL collate utf8mb4_bin;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t_collation where emp_name = 'tom';
+----------+
| emp_name |
+----------+
| tom      |
+----------+
1 row in set (0.00 sec)

mysql> #或者
mysql> select * from t_collation where emp_name = ('tom' collate utf8_bin);

临时生效

mysql> select 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> set names utf8 collate utf8_bin; 
Query OK, 0 rows affected (0.00 sec)

mysql> select 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

2.5.字符集建议

非常肯定只有中文终端用户时，可选择gbk / gb2312
为了方便数据迁移、以及多种终端展示，最好是utf8
字符无需区分大小写时，采用默认的xx_ci校验集可以，否则选择xx_bin校验集(生产环境中,尽量不要修改校验集)
5.6之前默认字符集是latin1，该字符集存放汉字是分开存放，以至于检索结果时不够精确，好处就是节省空间，不推荐使用

2.6.字符集最佳实践

2.6.1.查看字符集设置

操作系统字符集：

# locale
LANG=en_US.UTF-8
LC_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"

系统支持所有的字符集

# locale -a
aa_DJ
aa_DJ.iso88591
aa_DJ.utf8
aa_ER
...... 省略 ......

修改字符集

# cat /etc/sysconfig/i18n 
LANG="en_US.UTF-8"
SYSFONT="latarcyrheb-sun16"

# export LANG="en_US.UTF-8"

数据库字符集：

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.6.30-76.3, for Linux (x86_64) using  6.0

Connection id:      13
Current database:   
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.6.30-76.3-log Percona Server (GPL), Release 76.3, Revision 3850db5
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /var/wd/db10059/my10059.sock
Uptime:         55 min 28 sec

Threads: 1  Questions: 87  Slow queries: 0  Opens: 70  Flush tables: 1  Open tables: 63  Queries per second avg: 0.026
--------------

mysql> show global variables like '%char%';
+--------------------------+-------------------------------------+
| Variable_name            | Value                               |
+--------------------------+-------------------------------------+
| character_set_client     | utf8                                |
| character_set_connection | utf8                                |
| character_set_database   | utf8                                |
| character_set_filesystem | binary                              |
| character_set_results    | utf8                                |
| character_set_server     | utf8                                |
| character_set_system     | utf8                                |
| character_sets_dir       | /usr/share/percona-server/charsets/ |
+--------------------------+-------------------------------------+
8 rows in set (0.01 sec)

修改数据库字符集
临时生效

mysql> set character_set = 'gbk';
mysql> set character_set_client = 'gbk';

全局生效

mysql> set global character_set_client = 'gbk';
Query OK, 0 rows affected (0.00 sec)

永久生效

# vim /etc/my.cnf 
character-set-server=utf8

客户端字符集：
客户端可以是MySQL终端、也可以是program，我们分别以xshell和python语言为例：

programs

#!/bin/env python2.7
# _*_coding:utf-8 _*_
def get_otter_delay(*args):
    try:
        conn = MySQLdb.connect(host=args[0], port=args[1], db=args[2], user=args[3], passwd=args[4], charset='utf8')
        cur = conn.cursor()
        sql = 'select DELAY_TIME from otter_mgr.DELAY_STAT where GMT_CREATE = (select max(GMT_CREATE) from otter_mgr.DELAY_STAT)'
        cur.execute(sql)
        result_info = cur.fetchone()
        return result_info
        cur.close()
        conn.close()
    except Exception as e:
        print e

xshell

Encoding --> Unocode(utf-8)

2.6.2.常见乱码处理

一般来说工作中由于字符集不统一造成的乱码或者潜在风险是需要将已乱码的数据进行转码的。
处理乱码原则都是小字符集转换大字符集
latin1 --> utf8
gbk --> utf8
latin1 --> gbk
lation1字符集环境：LANG=US.ISO_8859-1
gbk字符集环境：LANG=en_US.GBK | LANG=zh_CN.GBK
utf8字符集环境：LANG=en_US.UTF-8

2.6.3.字符集转换

场景：数据库中两个不同字符集的数据库

3.数据类型

3.1.Integer类型

3.1.1.Integer类型分类

图片来自MySQL官档

3.1.2.Integer类型特性

int(n)和zerofill
int(n)： n=任何数，n仅代表显示宽度，不表示存储的数字的长度的上限。
zerofill：表示当存储的数字长度 < N时，用数字0填充左边，直至补满长度N；当存储数字的长度超过N时，按照实际存储的数字显示。
注意：int(n)中的N和zerofill配合才有意义，且仅仅是显示的时候才有意义，和实际存储没有关系，不会去截取数字的长度。

mysql> create  table int_n (a int(3) zerofill);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into int_n values (2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into int_n values (2017);
Query OK, 1 row affected (0.00 sec)

mysql> select * from int_n;
+------+
| a    |
+------+
|  002 |
| 2017 |
+------+
2 rows in set (0.00 sec)

mysql> select CAST(0x7E1 AS UNSIGNED);
+-------------------------+
| CAST(0x7E1 AS UNSIGNED) |
+-------------------------+
|                    2017 |
+-------------------------+

auto_increment(自动增长)
auto_increment属性必须和唯一索引一起才能使用，用于约束写入的数必须没有重复性。
一张表中只能有一个auto_increment属性，并且auto_increment作用域仅仅是表。
Innodb engine的表中，最好每一个表都带有一个auto_increment属性，用于防止PAGE分裂。

mysql> create table t_auto_incrememnt (a int auto_increment);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> create table t_auto_increment (a int auto_increment, unique key(a));
Query OK, 0 rows affected (0.03 sec)
mysql> create table t_auto_increment (a int auto_increment,b int auto_increment ,unique key(a), primary key(b));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql> insert into t_auto_increment values(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_auto_increment values(0);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_auto_increment values(-1);
Query OK, 1 row affected (0.04 sec)

mysql> select * from t_auto_increment;
+----+
| a  |
+----+
| -1 |
|  1 |
|  2 |
+----+

mysql> insert into t_auto_increment values("0");
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_auto_increment;
+----+
| a  |
+----+
| -1 |
|  1 |
|  2 |
|  3 |
+----+
4 rows in set (0.00 sec)

mysql> insert into t_auto_increment values (10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_auto_increment values (10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_auto_increment values (0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_auto_increment;
+----+
| a  |
+----+
| -1 |
|  1 |
|  2 |
|  3 |
| 10 |
| 11 |
+----+
6 rows in set (0.01 sec)

unsigned or signed(有字符或无字符)
unsigned和signed最大的区别一个是int范围不一致，还有个是存在正负数区别；既然允许正负数那么在运算时就需要注意。

mysql> create table t_unsigned(a int unsigned, b int signed);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_unsigned values (1,2);
Query OK, 1 row affected (0.00 sec)

mysql> select a-b from t_unsigned;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test_db`.`t_unsigned`.`a` - `test_db`.`t_unsigned`.`b`)

mysql> set session sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t_unsigned values (-10,-10);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t_unsigned;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    0 |  -10 |
+------+------+
2 rows in set (0.00 sec)

mysql> set sql_mode = 'no_unsigned_subtraction';
mysql> select * from t_unsigned;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    0 |  -10 |
+------+------+
2 rows in set (0.00 sec)

3.2.Number类型

图片来自网络

FLOAT：单精度类型
存储空间：4 字节
精确性：低
DOUBLE：双精度类型
占用空间：8 字节
精确性：低，比FLOAT高
DECIMAL：高精度类型
占用空间：变长
精确性：非常高

mysql> create table t_numbers (c1 float(9,3), c2 DOUBLE(9,3), c3 DECIMAL(9,3));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_numbers values (123456.8,123456.8,123456.8);
Query OK, 1 row affected (0.01 sec)
  
mysql> select * from t_numbers;
+------------+------------+------------+
| c1         | c2         | c3         |
+------------+------------+------------+
| 123456.797 | 123456.800 | 123456.800 |
+------------+------------+------------+
1 row in set (0.00 sec)


mysql> insert into t_numbers values (999.0009,999.0009,999.0009);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from t_numbers;
+------------+------------+------------+
| c1         | c2         | c3         |
+------------+------------+------------+
| 123456.797 | 123456.800 | 123456.800 |
|    999.001 |    999.001 |    999.001 |
+------------+------------+------------+
2 rows in set (0.00 sec)

mysql> insert into t_numbers values (1234567.89,1234567.89,1234567.89);
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'c1' at row 1 |
| Warning | 1264 | Out of range value for column 'c2' at row 1 |
| Warning | 1264 | Out of range value for column 'c3' at row 1 |
+---------+------+---------------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from t_numbers;
+-------------+------------+------------+
| c1          | c2         | c3         |
+-------------+------------+------------+
|  123456.797 | 123456.800 | 123456.800 |
|     999.001 |    999.001 |    999.001 |
| 1000000.000 | 999999.999 | 999999.999 |
+-------------+------------+------------+
3 rows in set (0.00 sec)

3.3.String类型

3.3.1.String分类

类型	说明	N的含义	是否有字符集	最大长度
CHAR(N)	定长字符	字符	是	255
VARCHAR(N)	变长字符	字符	是	16384
BINARY(N)	定长二进制字节	字节	否	255
VARBINARY(N)	变长二进制字节	字节	否	16384
TINYBLOB(N)	二进制大对象	字节	否	256
BLOB(N)	二进制大对象	字节	否	16K
MEDIUMBLOB(N)	二进制大对象	字节	否	16M
LONGBLOB(N)	二进制大对象	字节	否	4G
TINYTEXT(N)	大对象	字节	是	256
TEXT(N)	大对象	字节	是	16K
MEDIUMTEXT(N)	大对象	字节	是	16M
LONGTEXT(N)	大对象	字节	是	4G

3.3.2.char类型

char(N)：N代表的是字符个数(也叫字符长度)、而非bytes
char(N)：是固定长存储,占用定长的存储空间,不足的部分用空格填充；MySQL处理char(N)类型时，需要将空格strip掉后return。
存储空间：char(N)类型的存储空间和字符集有关系，结合刚才字符集的知识点，一个中文在utf8字符集中占用3个bytes、gbk占用2个bytes、数字和字符统一用一个字符表示。
存储机制：在不够N长度时，MySQL在存储数据时，需要用填充特殊的空格，而非十六进制的20

mysql> create table t_char(c1 char(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_char values ('Hello  ');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_char values ('套路');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_char values ('套路a');
Query OK, 1 row affected (0.00 sec)

mysql> select c1,length(c1) from t_char;
+---------+------------+
| c1      | length(c1) |
+---------+------------+
| Hello   |          5 |
| 套路    |          6 |
| 套路a   |          7 |
+---------+------------+
3 rows in set (0.00 sec)

mysql> select c1,hex(c1) from t_char;
+---------+----------------+
| c1      | hex(c1)        |
+---------+----------------+
| Hello   | 48656C6C6F     |
| 套路    | E5A597E8B7AF   |
| 套路a   | E5A597E8B7AF61 |
+---------+----------------+
3 rows in set (0.00 sec)

# hexdump -C t_char.ibd 
00000000  60 a0 3d d7 00 00 00 00  00 00 00 00 00 00 00 00  |`.=.............|
00000010  00 00 00 00 00 1b c8 45  00 08 00 00 00 00 00 00  |.......E........|
00000020  00 00 00 00 00 15 00 00  00 15 00 00 00 00 00 00  |................|
00000030  00 06 00 00 00 40 00 00  00 00 00 00 00 04 00 00  |.....@..........|
00000040  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 00 00  |................|
00000050  00 01 00 00 00 00 00 9e  00 00 00 00 00 9e 00 00  |................|
00000060  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 00 00  |................|
00000070  00 00 00 00 00 03 00 00  00 00 ff ff ff ff 00 00  |................|
00000080  ff ff ff ff 00 00 00 00  00 01 00 00 00 02 00 26  |...............&|
00000090  00 00 00 02 00 26 00 00  00 00 00 00 00 00 ff ff  |.....&..........|
000000a0  ff ff 00 00 ff ff ff ff  00 00 00 00 00 02 aa ff  |................|
000000b0  ff ff ff ff ff ff ff ff  ff ff ff ff ff ff 00 00  |................|
000000c0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00003ff0  00 00 00 00 00 00 00 00  4f ca b0 4a 00 1b c8 45  |........O..J...E|
00004000  94 19 77 94 00 00 00 01  00 00 00 00 00 00 00 00  |..w.............|
00004010  00 00 00 00 00 1b b9 bc  00 05 00 00 00 00 00 00  |................|
00004020  00 00 00 00 00 15 00 00  00 00 00 00 00 00 00 00  |................|
00004030  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00007ff0  00 00 00 00 00 00 00 00  4b ee 24 56 00 1b b9 bc  |........K.$V....|
00008000  b1 2b 78 67 00 00 00 02  00 00 00 00 00 00 00 00  |.+xg............|
00008010  00 00 00 00 00 1b c8 45  00 03 00 00 00 00 00 00  |.......E........|
00008020  00 00 00 00 00 15 ff ff  ff ff 00 00 ff ff ff ff  |................|
00008030  00 00 00 00 00 00 00 00  00 01 00 00 00 00 00 00  |................|
00008040  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 00 00  |................|
*
00008060  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 05 d6  |................|
00008070  69 d2 00 00 00 03 ff ff  ff ff ff ff ff ff ff ff  |i...............|
00008080  ff ff ff ff ff ff ff ff  ff ff ff ff ff ff ff ff  |................|
*
000080f0  ff ff 00 00 00 00 00 00  00 02 00 00 00 00 00 00  |................|
00008100  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 00 00  |................|
*
00008120  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 05 d6  |................|
00008130  69 d2 ff ff ff ff ff ff  ff ff ff ff ff ff ff ff  |i...............|
00008140  ff ff ff ff ff ff ff ff  ff ff ff ff ff ff ff ff  |................|
*
000081b0  ff ff 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
000081c0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
0000bff0  00 00 00 00 00 00 00 00  98 3c a8 f6 00 1b c8 45  |.........<.....E|
0000c000  2d b3 9e 95 00 00 00 03  ff ff ff ff ff ff ff ff  |-...............|
0000c010  00 00 00 00 00 1b e3 49  45 bf 00 00 00 00 00 00  |.......IE.......|
0000c020  00 00 00 00 00 15 00 02  00 e4 80 05 00 00 00 00  |................|
0000c030  00 c7 00 02 00 02 00 03  00 00 00 00 00 00 00 00  |................|
0000c040  00 00 00 00 00 00 00 00  00 22 00 00 00 15 00 00  |........."......|
0000c050  00 02 00 f2 00 00 00 15  00 00 00 02 00 32 01 00  |.............2..|
0000c060  02 00 1c 69 6e 66 69 6d  75 6d 00 04 00 0b 00 00  |...infimum......|
0000c070  73 75 70 72 65 6d 75 6d  0a 00 00 00 10 00 24 00  |supremum......$.|
0000c080  00 00 00 03 18 00 00 00  00 0d ad fc 00 00 01 b4  |................|
0000c090  00 84 48 65 6c 6c 6f 20  20 20 20 20 0a 00 00 00  |..Hello     ....|      <-- 填充了5个空格
0000c0a0  18 00 24 00 00 00 00 03  19 00 00 00 00 0d ae fd  |..$.............|
0000c0b0  00 00 01 b5 00 84 e5 a5  97 e8 b7 af 20 20 20 20  |............    |      <-- 填充了4个空格
0000c0c0  0a 00 00 00 20 ff a9 00  00 00 00 03 1a 00 00 00  |.... ...........|
0000c0d0  00 0d b3 80 00 00 01 2d  00 84 e5 a5 97 e8 b7 af  |.......-........|
0000c0e0  61 20 20 20 00 00 00 00  00 00 00 00 00 00 00 00  |a   ............|      <-- 填充了3个空格
0000c0f0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
0000fff0  00 00 00 00 00 70 00 63  b0 96 41 5b 00 1b e3 49  |.....p.c..A[...I|
00010000  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00018000

3.3.3.varcahr类型

varchar(N)：N代表的是字符个数(也叫字符长度)、而非bytes
varchar(N)：是变长存储,仅使用必要的存储空间.
存储空间：varchar(N)类型的存储空间和字符集有关系，结合刚才字符集的知识点，一个中文在utf8字符集中占用3个bytes、gbk统一占用2个bytes、数字和字符一个字符表示。
存储机制：varchar(N)字段存储实际是从第二个字节开始存储,然后用1到2个字节表示实际长度,剩下的才是可以存储数据的范围,因此最大可用存储范围是65535-3=65532字节;第一个字节标识是否为空.(长度小于255字节,使用一个字节来表示长度;大于255字节使用两个字节来表示长度)

mysql> create table t_varchar (c1 varchar(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_varchar values ('123');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_varchar values ('2数据库');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_varchar values ('mysql数据库  ');
Query OK, 1 row affected (0.00 sec)

mysql> select c1,hex(c1) from t_varchar;
+------------------+----------------------------------+
| c1               | hex(c1)                          |
+------------------+----------------------------------+
| 123              | 313233                           |
| 2数据库          | 32E695B0E68DAEE5BA93             |
| mysql数据库      | 6D7973716CE695B0E68DAEE5BA932020 |        <--20是十六进制表的空格
+------------------+----------------------------------+
3 rows in set (0.00 sec)

mysql> select concat(c1,"+") from t_varchar;
+-------------------+
| concat(c1,"+")    |
+-------------------+
| 123+              |
| 2数据库+          |
| mysql数据库  +    |
+-------------------+
3 rows in set (0.00 sec)

mysql> select hex(' ') from dual;
+----------+
| hex(' ') |
+----------+
| 20       |
+----------+
1 row in set (0.00 sec)

# hexdump -C t_varchar.ibd 
00000000  8f 14 14 4c 00 00 00 00  00 00 00 00 00 00 00 00  |...L............|
00000010  00 00 00 00 00 1c 23 72  00 08 00 00 00 00 00 00  |......#r........|
00000020  00 00 00 00 00 17 00 00  00 17 00 00 00 00 00 00  |................|
00000030  00 06 00 00 00 40 00 00  00 00 00 00 00 04 00 00  |.....@..........|
00000040  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 00 00  |................|
00000050  00 01 00 00 00 00 00 9e  00 00 00 00 00 9e 00 00  |................|
00000060  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 00 00  |................|
00000070  00 00 00 00 00 03 00 00  00 00 ff ff ff ff 00 00  |................|
00000080  ff ff ff ff 00 00 00 00  00 01 00 00 00 02 00 26  |...............&|
00000090  00 00 00 02 00 26 00 00  00 00 00 00 00 00 ff ff  |.....&..........|
000000a0  ff ff 00 00 ff ff ff ff  00 00 00 00 00 02 aa ff  |................|
000000b0  ff ff ff ff ff ff ff ff  ff ff ff ff ff ff 00 00  |................|
000000c0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00003ff0  00 00 00 00 00 00 00 00  b4 c7 1d c0 00 1c 23 72  |..............#r|
00004000  78 3a 46 36 00 00 00 01  00 00 00 00 00 00 00 00  |x:F6............|
00004010  00 00 00 00 00 1c 17 fb  00 05 00 00 00 00 00 00  |................|
00004020  00 00 00 00 00 17 00 00  00 00 00 00 00 00 00 00  |................|
00004030  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00007ff0  00 00 00 00 00 00 00 00  78 83 2a 58 00 1c 17 fb  |........x.*X....|
00008000  64 08 d6 ea 00 00 00 02  00 00 00 00 00 00 00 00  |d...............|
00008010  00 00 00 00 00 1c 23 72  00 03 00 00 00 00 00 00  |......#r........|
00008020  00 00 00 00 00 17 ff ff  ff ff 00 00 ff ff ff ff  |................|
00008030  00 00 00 00 00 00 00 00  00 01 00 00 00 00 00 00  |................|
00008040  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 00 00  |................|
*
00008060  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 05 d6  |................|
00008070  69 d2 00 00 00 03 ff ff  ff ff ff ff ff ff ff ff  |i...............|
00008080  ff ff ff ff ff ff ff ff  ff ff ff ff ff ff ff ff  |................|
*
000080f0  ff ff 00 00 00 00 00 00  00 02 00 00 00 00 00 00  |................|
00008100  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 00 00  |................|
*
00008120  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 05 d6  |................|
00008130  69 d2 ff ff ff ff ff ff  ff ff ff ff ff ff ff ff  |i...............|
00008140  ff ff ff ff ff ff ff ff  ff ff ff ff ff ff ff ff  |................|
*
000081b0  ff ff 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
000081c0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
0000bff0  00 00 00 00 00 00 00 00  37 83 3a e8 00 1c 23 72  |........7.:...#r|
0000c000  f7 53 51 49 00 00 00 03  ff ff ff ff ff ff ff ff  |.SQI............|
0000c010  00 00 00 00 00 1c 33 b0  45 bf 00 00 00 00 00 00  |......3.E.......|
0000c020  00 00 00 00 00 17 00 02  00 e3 80 05 00 00 00 00  |................|
0000c030  00 c0 00 02 00 02 00 03  00 00 00 00 00 00 00 00  |................|
0000c040  00 00 00 00 00 00 00 00  00 24 00 00 00 17 00 00  |.........$......|
0000c050  00 02 00 f2 00 00 00 17  00 00 00 02 00 32 01 00  |.............2..|
0000c060  02 00 1c 69 6e 66 69 6d  75 6d 00 04 00 0b 00 00  |...infimum......|
0000c070  73 75 70 72 65 6d 75 6d  03 00 00 00 10 00 1d 00  |supremum........|
0000c080  00 00 00 03 1f 00 00 00  00 0d e0 99 00 00 01 70  |...............p|
0000c090  00 84 31 32 33 0a 00 00  00 18 00 24 00 00 00 00  |..123......$....|
0000c0a0  03 20 00 00 00 00 0d e1  9a 00 00 01 be 00 84 32  |. .............2|
0000c0b0  e6 95 b0 e6 8d ae e5 ba  93 10 00 00 00 20 ff b0  |............. ..|
0000c0c0  00 00 00 00 03 21 00 00  00 00 0d e6 9d 00 00 01  |.....!..........|
0000c0d0  bf 00 84 6d 79 73 71 6c  e6 95 b0 e6 8d ae e5 ba  |...mysql........|
0000c0e0  93 20 20 00 00 00 00 00  00 00 00 00 00 00 00 00  |.  .............|
0000c0f0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
0000fff0  00 00 00 00 00 70 00 63  96 b2 56 aa 00 1c 33 b0  |.....p.c..V...3.|
00010000  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00018000

范围限制

a) 存储限制
varchar 字段是将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度（长度超过255时需要2个字节），因此每行最多存储最多一共存储65535个字节。

b) 编码长度限制
varchar中,第一个字节不能用，后面1~2字节表示变长长度。
字符类型若为gbk，每个字符最多占2个字节，最大长度不能超过(65525-1-2) / 2 = 32766；
字符类型若为utf8，每个字符最多占3个字节，最大长度不能超过(65525-1-2) / 3 = 21845；

c) 行长度限制
导致实际应用中varchar长度限制的是一个行定义的长度。 MySQL要求一个行的定义长度不能超过65535。若定义的表长度超过这个值，则提示：
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs。

实际长度计算示例：

a) 若一个表只有一个varchar类型，如定义为create table t1(c varchar(n)) charset=gbk;
则此处n的最大值为(65535-1-2)/2= 32766
减1的原因是实际行存储从第二个字节开始;
减2的原因是varchar头部的2个字节表示长度;
除2的原因是字符编码是gbk

b) 若一个表定义为create table t2(c int, c2 char(30), c3 varchar(n)) charset=utf8;
则此处N的最大值为(65535-1-2-4-30*3)/3=21812
减1和减2与上例相同;
减4的原因是int类型的c占4个字节;
减30*3的原因是char(30)占用90个字节,编码是utf8

varchar(10)和varchar(100)有区别吗?

对于VARCHAR数据类型来说,硬盘上的存储空间虽然都是根据实际字符长度来分配存储空间的,但是对于内存来说,则不是.内存使用固定大小的内存块来保存值.简单的说,就是使用字符类型中定义的长度,即100个字符空间.显然,这对于排序或者临时表(这些内容都需要通过内存来实现)作业会产生比较大的不利影响.

3.3.4.BLOB和TEXT类型

BLOB和TEXT以及varchar类型是变长数据类型
不能对全部长度的字符串进行索引，只对最前面max_sort_length字节做排序,默认是1024字节；
在BLOB和TEXT上创建索引时，必须指定索引前缀的长度；

mysql> create table t_text (a int primary key, b text, key(b));
ERROR 1170 (42000): BLOB/TEXT column 'b' used in key specification without a key length

mysql> create table t_text (a int primary key, b text, key(b(64)));
Query OK, 0 rows affected (0.00 sec)

mysql> select @@max_sort_length;
+-------------------+
| @@max_sort_length |
+-------------------+
|              1024 |
+-------------------+
1 row in set (0.00 sec)

3.4.集合类型

集合类型ENUM 和 SET
ENUM类型最多允许65536个值
SET类型最多允许64个值
通过sql_mode参数可以用户约束检查

3.4.1.ENUM类型

mysql> create table employees (user char(10), kpi_level enum('A','2A','B','C'));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into employees values ('Tom','A');
Query OK, 1 row affected (0.00 sec)

mysql> insert into employees values ('Jack','B');
Query OK, 1 row affected (0.00 sec)

mysql> insert into employees values ('Eirc','2A');
Query OK, 1 row affected (0.00 sec)

mysql> insert into employees values ('bug','D');
ERROR 1265 (01000): Data truncated for column 'kpi_level' at row 1

mysql> #排序测试
mysql> select * from employees order by kpi_level ; 
+------+-----------+
| user | kpi_level |
+------+-----------+
| Tom  | A         |
| Eirc | 2A        |
| Jack | B         |
+------+-----------+
3 rows in set (0.01 sec)

mysql> 使用ascii排序
mysql> select * from employees order by cast(kpi_level as char) asc;
+------+-----------+
| user | kpi_level |
+------+-----------+
| Eirc | 2A        |
| Tom  | A         |
| Jack | B         |
+------+-----------+

3.4.2.SET类型

mysql> CREATE TABLE `TestSet` (  
    ->   `Id` int(4) NOT NULL AUTO_INCREMENT,  
    ->   `set1` set('ABC','1111','2222','XXX') DEFAULT NULL,  
    ->   PRIMARY KEY (`Id`)  
    -> ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; 
Query OK, 0 rows affected (0.02 sec)

mysql> insert into TestSet (set1) values ('ABC');
Query OK, 1 row affected (0.00 sec)

mysql> insert into TestSet (set1) values ('1111');
Query OK, 1 row affected (0.01 sec)

mysql> insert into TestSet (set1) values ('2222');
Query OK, 1 row affected (0.00 sec)

mysql> insert into TestSet (set1) values ('XXX');
Query OK, 1 row affected (0.01 sec)

mysql> select * from TestSet order by set1;
+----+------+
| Id | set1 |
+----+------+
|  3 | ABC  |
|  4 | 1111 |
|  5 | 2222 |
|  6 | XXX  |
+----+------+
4 rows in set (0.00 sec)

mysql> select * from TestSet order by cast(set1 as char);
+----+------+
| Id | set1 |
+----+------+
|  4 | 1111 |
|  5 | 2222 |
|  3 | ABC  |
|  6 | XXX  |
+----+------+
4 rows in set (0.01 sec)

3.5.日期类型

3.5.1.日期分类

DATETIME和TIMESTAMP
TIMESTAMP和时区有关(根据client时区,自动转换成当地时间)
5.5版本后timestamp新特性
在MySQL5.5里(或更老的版本MySQL5.1)timestamp类型一个表里只允许一列字段拥有自动插入时间和自动更新时间
MySQL5.6以后,可以有多列有上述属性

日期类型	占用空间(byte)（<5.6）	占用空间(byte)(>=5.6)	表示范围
DATETIME	8	5 + 微秒存储空间	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
DATE	3	3	1000-01-01 ~ 9999-12-31
TIMESTAMP	4	4 + 微秒存储空间	1970-01-01 08:00:01UTC ~ 2038-01-19 03:14:07UTC
YEAR	1	1	YEAR(2):1970-2070, YEAR(4):1901-2155
TIME	3	3 + 微秒存储空间	-838:59:59 ~ 838:59:59

微秒位数	所需存储空间
0	0
2	1 byte
4	2 bytes
6	3 bytes

3.5.2.时区特性

datetime和timestamp时区

mysql> CREATE TABLE `t8` (
    -> `id1` timestamp NOT NULL default CURRENT_TIMESTAMP,
    -> `id2` datetime default NULL);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t8 values (now(),now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from t8;
+---------------------+---------------------+
| id1                 | id2                 |
+---------------------+---------------------+
| 2017-08-13 04:21:17 | 2017-08-13 04:21:17 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set (0.00 sec)

mysql> quit
Bye

# #看一下系统时区
# date -R
Sun, 13 Aug 2017 04:19:46 +0800

# mysql
mysql> #设置一下MySQL时区
mysql> set time_zone='+9:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t8;
+---------------------+---------------------+
| id1                 | id2                 |
+---------------------+---------------------+
| 2017-08-13 05:21:17 | 2017-08-13 04:21:17 |
+---------------------+---------------------+
1 row in set (0.01 sec)

3.5.3.时间函数

函数名	函数说明	备注
NOW	返回SQL执行时的时间	如果不考虑其他因素，可以理解为写完SQL，敲下回车瞬间的时间
CURRENT_TIMESTAMP	与NOW()函数同义
SYSDATE	返回函数执行时的时间	MySQL处理你的函数时的时间，统一SQL语句中，大于NOW
DATA_ADD(date, interval expr uint)	增加时间
DATA_SUB(date, interval expr uint)	减少时间	可用ADD，然后unit给负数
DATE FORMAT	格式化时间

NOW和SYSDATE的区别
now函数：调用函数开始时间
sysdate函数：调用函数结束时间

mysql> select now(6),sysdate(6),sleep(5),now(6),sysdate(6);
+----------------------------+----------------------------+----------+----------------------------+----------------------------+
| now(6)                     | sysdate(6)                 | sleep(5) | now(6)                     | sysdate(6)                 |
+----------------------------+----------------------------+----------+----------------------------+----------------------------+
| 2017-08-13 05:34:09.937585 | 2017-08-13 05:34:09.938744 |        0 | 2017-08-13 05:34:09.937585 | 2017-08-13 05:34:14.944655 |
+----------------------------+----------------------------+----------+----------------------------+----------------------------+
1 row in set (5.02 sec)

日期函数

set @dt = now();

select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 minute); 
select date_add(@dt, interval 1 hour); -- add 1 hour
select date_add(@dt, interval 1 day); -- add 1 day
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 year);
select date_add(@dt, interval 1 quarter); --不常用

select date_add(@dt, interval -1 day); -- subtract 1day
select date_sub(@dt, interval 1 minute); -- subtract 1minute

select DATE_FORMAT((select now(6)), 'partition_%Y_%m_%d_%H_%i_%s');

3.5.4.时间更新(5.6新特性)

TIMESTAMP在MySQL5.5中的行为

第一个未设置默认值的TIMESTAMP NOT NULL字段隐式默认值： CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
后面未设置默认值的TIMESTAMP NOT NULL字段隐式默认值：0000-00-00 00:00:00
TIMESTAMP NOT NULL字段插入NULL时，会使用隐式默认值： CURRENT_TIMESTAMP
不支持多个CURRENT_TIMESTAMP 默认值

TIMESTAMP在MySQL5.6中的行为

支持多个CURRENT_TIMESTAMP 默认值
可以兼容5.5的行为，支持隐性默认值
可以去掉隐性默认值：explicit_defaults_for_timestamp=1
如果不加，5.6默认启动时，服务器会给出一个警告。
[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).

mysql> create table t_timestamp (t1 timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,t2 timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t_timestamp \G
*************************** 1. row ***************************
       Table: t_timestamp
Create Table: CREATE TABLE `t_timestamp` (
  `t1` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `t2` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into t_timestamp (t1) values (now());
Query OK, 1 row affected (0.03 sec)

mysql> select * from t_timestamp;
+---------------------+---------------------+
| t1                  | t2                  |
+---------------------+---------------------+
| 2017-08-13 04:53:30 | 2017-08-13 04:53:30 |
+---------------------+---------------------+
1 row in set (0.00 sec)

时间范围示例

创建一张表，出现以下错误:ERROR 1067 (42000): Invalid default value for 'M_CONFIRM_TIME'

mysql> CREATE TABLE `SETTLE_RECV` (
    ->   `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    ->   `CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '生成时间',
    ->   `UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
    ->   `M_CONFIRM_TIME` timestamp NOT NULL DEFAULT '1970-01-01 01:00:00' COMMENT '商户确认时间',
    ->   `VERIFY_TIME` timestamp NOT NULL DEFAULT '1970-01-01 01:00:00' COMMENT '销账时间',
    ->   `SETTLE_STATUS` int(11) NOT NULL DEFAULT '0' COMMENT '结算单状态',
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB CHARSET=utf8 COMMENT='xxx详情表';
ERROR 1067 (42000): Invalid default value for 'M_CONFIRM_TIME'

问题分析：为什么会提示无效的默认值呢，尝试改一成'0000-00-00 00:00:00'试试。
问题虽然能够得到解决，但是此方法会造成应用程序错误，如：JAVA支持最早的时间是：'1970-01-01 00:00:00'
为什么几乎所有的语言最早支持的时间是:'1970-01-01 00:00:00'呢？ 1970年正式UNIX诞生；所以计算机时间也就是从那一年开始的；

mysql> CREATE TABLE `SETTLE_RECV` (
    ->   `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    ->   `CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '生成时间',
    ->   `UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
    ->   `M_CONFIRM_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '商户确认时间',
    ->   `VERIFY_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '销账时间',
    ->   `SETTLE_STATUS` int(11) NOT NULL DEFAULT '0' COMMENT '结算单状态',
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB CHARSET=utf8 COMMENT='xxx详情表';
Query OK, 0 rows affected (0.01 sec)

mysql> insert into SETTLE_RECV (SETTLE_STATUS) values ('1');
Query OK, 1 row affected (0.01 sec)

mysql> select * from SETTLE_RECV;
+----+---------------------+---------------------+---------------------+---------------------+---------------+
| ID | CREATE_TIME         | UPDATE_TIME         | M_CONFIRM_TIME      | VERIFY_TIME         | SETTLE_STATUS |
+----+---------------------+---------------------+---------------------+---------------------+---------------+
|  1 | 2017-08-30 10:28:22 | 2017-08-30 10:28:22 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |             1 |
+----+---------------------+---------------------+---------------------+---------------------+---------------+
1 row in set (0.00 sec)

问题解决：MySQL的timestamp范围是UTC的'1970-01-01 00:00:01'开始，为什么MySQL还创建异常呢？MySQL BUG？ No No No，仔细的同学看到了是UTC时间，所以我们要加+时区的position，完美解决。

mysql> show global variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> system date -R
Wed, 30 Aug 2017 10:40:26 +0800

mysql> CREATE TABLE `SETTLE_RECV` (
    ->   `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    ->   `CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '生成时间',
    ->   `UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
    ->   `M_CONFIRM_TIME` timestamp NOT NULL DEFAULT '1970-01-01 08:00:01' COMMENT '商户确认时间',
    ->   `VERIFY_TIME` timestamp NOT NULL DEFAULT '1970-01-01 08:00:01' COMMENT '销账时间',
    ->   `SETTLE_STATUS` int(11) NOT NULL DEFAULT '0' COMMENT '结算单状态',
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB CHARSET=utf8 COMMENT='xxx详情表';
Query OK, 0 rows affected (0.02 sec)

或者修改数据库时区

mysql> set global time_zone= '+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> set session time_zone= '+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `SETTLE_RECV` (
    ->   `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    ->   `CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '生成时间',
    ->   `UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
    ->   `M_CONFIRM_TIME` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01' COMMENT '商户确认时间',
    ->   `VERIFY_TIME` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01' COMMENT '销账时间',
    ->   `SETTLE_STATUS` int(11) NOT NULL DEFAULT '0' COMMENT '结算单状态',
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB CHARSET=utf8 COMMENT='xxx详情表';
Query OK, 0 rows affected (0.00 sec)

4.权限管理

4.1.MySQL权限概念

图片来自原创

MySQL中一个用户是否能够链接MySQL，除了用户名/秘密外，还需要网络位置的认证；在MySQL中，实际上我们可以理解他们是三个完全不同的账号，192.168.1.0实际上代表的就是网络位，书写格式中主机位要用'%'代替，如：
david@192.168.1.0
david@localhost
david@127.0.0.1
IP addresses that satisfy this condition range from 192.58.197.0 to 192.58.197.255.
A netmask typically begins with bits set to 1, followed by bits set to 0. Examples:
192.0.0.0/255.0.0.0: Any host on the 192 class A network
192.168.0.0/255.255.0.0: Any host on the 192.168 class B network
192.168.1.0/255.255.255.0: Any host on the 192.168.1 class C network
192.168.1.1: Only the host with this specific IP address

4.2.权限介绍

4.2.1.授权信息表

These mysql database tables contain grant information:

user: User accounts, global privileges, and other non-privilege columns
db: Database-level privileges
tables_priv: Table-level privileges
columns_priv: Column-level privileges
procs_priv: Stored procedure and function privileges
proxies_priv: Proxy-user privileges

4.2.2.MySQL可用权限

常用权限：
SQL语句：SELECT、INSERT、UPDATE、DELETE、INDEX
存储过程：CREATE ROUTINE、ALTER ROUTINE、EXECUTE、TRIGGER
管理权限：SUPER、RELOAD、SHOW DATABASE、SHUTDOWN

更多权限资源请参考：https://dev.mysql.com/doc/refman/5.7/en/grant-tables.html

限制资源
用户每小时运行数据库的查询数量：MAX_QUERIES_PER_HOUR count
用户每小时的修改数据库数据的数量：MAX_UPDATES_PER_HOUR count
用户每小时打开新数据库连接的数量：MAX_CONNECTIONS_PER_HOUR count
用户连接MYSQL服务器的数量：MAX_USER_CONNECTIONS count

4.3.用户管理

4.3.1.添加用户

root用户创建一个用户

mysql> create user David@'192.168.1.%' identified by '123';
Query OK, 0 rows affected (0.01 sec)

mysql> alter user David@'192.168.1.%' with max_queries_per_hour 5;
Query OK, 0 rows affected (0.00 sec)

mysql> #取消系统资源限制
mysql> alter user David@'192.168.1.%' with max_queries_per_hour 0;
Query OK, 0 rows affected (0.00 sec)

David用户测试限制

# mysql -uDavid -p123

mysql> select user();
ERROR 1226 (42000): User 'David' has exceeded the 'max_questions' resource (current value: 5)

清空所有账户当前的资源记数器

mysql> flush user_resources;
Query OK, 0 rows affected (0.05 sec)

查询用户权限

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for David@'192.168.1.%';
+---------------------------------------------+
| Grants for David@192.168.1.%                |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'David'@'192.168.1.%' |
+---------------------------------------------+
1 row in set (0.00 sec)

修改密码

mysql> alter user new_user@192.168.1.160 identified by '456';
Query OK, 0 rows affected (0.00 sec)

4.3.2.修改用户属性

账号的锁定和解锁

mysql> alter user new_user@192.168.1.160 account lock;
Query OK, 0 rows affected (0.01 sec)

mysql> alter user new_user@192.168.1.160 account unlock;
Query OK, 0 rows affected (0.00 sec)

账号密码过期策略

详情请参考：https://dev.mysql.com/doc/refman/5.7/en/password-expiration-policy.html

mysql> alter user new_user@192.168.1.160 PASSWORD EXPIRE INTERVAL 90 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> alter user new_user@192.168.1.160 PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)

重命名数据库账号

mysql> rename user  new_user@'192.168.1.160' to new_user2@'192.168.1.160';
Query OK, 0 rows affected (0.01 sec)

4.3.3.删除用户

mysql> drop user David@'192.168.1.%';
Query OK, 0 rows affected (0.02 sec)

4.3.4.授权权限

可以授权一个不存在的用户，即创建用户并授权

mysql> grant all privileges on test_db.* to new_user@'192.168.1.160' identified by '123';
Query OK, 0 rows affected, 1 warning (0.06 sec)

创建已存在的用户，如何做?

mysql> select user,authentication_string,host from mysql.user ;
+-----------+-------------------------------------------+---------------+
| user      | authentication_string                     | host          |
+-----------+-------------------------------------------+---------------+
| root      | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | localhost     |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost     |
| student   | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | 192.168.1.%   |
| new_user  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | 192.168.1.%   |
+-----------+-------------------------------------------+---------------+

mysql> grant select,insert,update,delete on test_db.* to new_user@'192.168.1.%' identified BY password '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257';

授权一个列

mysql> grant select(request_time,api_version_id,request_count_outside) on test_db.product_api_pv_rt to read_only@'%' ly@'%' identified by '123';

授权一个能够为其他账户授权的账号

mysql> grant all privileges on *.* to admin@'localhost' identified by '123' with grant option;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

4.3.5.撤销权限

撤销和授权是一样的，统一将关键字 grant ... to 替换成 revoke ... from

mysql> revoke SELECT (request_count_outside, api_version_id, request_time) ON `test_db`.`product_api_pv_rt` from 'read_only'@'%';

4.4.proxy user

MySQL 5.7以后也有Role的概念，即为多个数据库用户授权一个Role，5.7称之为 Proxy user

mysql.proxies_priv仅仅是对Role的模拟，还没有Oracle那么完善。

mysql> create user dba@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> grant super on *.* to dba@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create user john@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> create user eric@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'dba'@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> grant proxy on dba@'localhost' to john@'localhost';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show grants for john@'localhost';
+--------------------------------------------------------+
| Grants for john@localhost                              |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'john'@'localhost'               |
| GRANT PROXY ON 'dba'@'localhost' TO 'john'@'localhost' |
+--------------------------------------------------------+
2 rows in set (0.00 sec)

作业

MySQL字符集转码时，需要注意什么?
答：
MySQL忘记密码怎么办?
答：--skip-grant-tables
为开发人员提供数据库连接串时，都应该提供什么?
答：
线上的业务程序需要分配什么权限?
答：
线上业务是否为开发人员开通数据库权限?如何有效控制开发人员对数据库的操作?
答：

扫描下方二维码关注本人微信号！欢迎大家交流学习！

Bruce.Liu

五、MySQL常用功能实践

1.字符集

1.1.字符集概述

1.2.常见字符集

1.3.分析乱码过程

2.MySQL字符集

2.1.MySQL常用字符集

gbk/gb2312

utf8/utf8mb4

latin1

2.2.MySQL字符集特性

2.3.MySQL字符集配置

2.4.MySQL校验规则

2.5.字符集建议

2.6.字符集最佳实践

2.6.1.查看字符集设置

2.6.2.常见乱码处理

2.6.3.字符集转换

3.数据类型

3.1.Integer类型

3.1.1.Integer类型分类

3.1.2.Integer类型特性

3.2.Number类型

3.3.String类型

3.3.1.String分类

3.3.2.char类型

3.3.3.varcahr类型

3.3.4.BLOB和TEXT类型

3.4.集合类型

3.4.1.ENUM类型

3.4.2.SET类型

3.5.日期类型

3.5.1.日期分类

3.5.2.时区特性

3.5.3.时间函数

3.5.4.时间更新(5.6新特性)

4.权限管理

4.1.MySQL权限概念

4.2.权限介绍

4.2.1.授权信息表

4.2.2.MySQL可用权限

4.3.用户管理

4.3.1.添加用户

4.3.2.修改用户属性

4.3.3.删除用户

4.3.4.授权权限

4.3.5.撤销权限

4.4.proxy user

作业

猜你喜欢

热点阅读