MySql生僻知识点

1. Enum数据类型，enum('a','b','abc')

Enum类型允许输入指定集合中的一个值，输入内容不区分大小写，最多允许65535个成员

2. Set类型， set('a','b','c')

Set类型允许输入指定集合中的一个或多个值，多个值之间用逗号分隔，最多允许64个成员。
set类型会自动去掉重复的数据，比如插入 ‘a,b,a,b,a’,库中只会存储 'a,b'

3. 安全的等于运算符 <=>

使用安全等于时 NULL<=>NULL 的值为1，效果等同于is null，而NULL=NULL的值为null

4. 正则表达式运算符 regexp

  -- 查询名字为aeiou中任意一个字母开头，ok结尾的数据
  SELECT * FROM t WHERE name REGEXP '^[aeiou]|ok$'; 
  -- 查询名字中包含mar的数据
  SELECT name FROM t WHERE name REGEXP 'mar';
  -- 查询名字以ok结尾的数据
  SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
  -- 查询名字以'st'为开头的数据
  SELECT name FROM person_tbl WHERE name REGEXP '^st';

5. if函数, if(value, v1, v2) 如果value为真，则返回v1，否则返回v2
   ifnull函数， ifnull(v1,v2) 如果v1不为null就返回v1，否则返回v2

-- 如果年龄大于20就返回man,否则返回boy
select if(age>20,'man','boy') from t 
-- 如果年龄字段为null，就返回0
select ifnull(age, 0) from t

6. 其他常用函数

函数	功能	使用举例
DATABASE()	返回当前数据库名	select database();
VERSION()	返回当前数据库版本	select version();
USER()	返回当前登录用户名	select user();
INET_ATON(ip)	返回ip地址的数字表示	select inet_aton('192.168.1.1'); 可以用来比较两个IP地址，比如想要知道两个IP段之间有多少个地址
INET_NTOA(num)	返回数字num代表的IP地址	select inet_ntoa(3232235777);
PASSWORD(str)	返回字符串str的加密版本	select password('aaa');
MD5(str)	返回字符串str的md5值	select md5('aaa')

7. 各种数据库存储引擎的特性

特点	MyISAM	InnoDB	MEMORY	MERGE	NDB
存储限制	有	64TB	有	没有	有
事物安全		支持
锁机制	表锁	行锁	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持		支持
全文索引	支持
集群索引		支持
数据缓存		支持	支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩	支持
空间使用	低	高	N/A	低	低
内存使用	低	高	中等	低	低
批量插入速度	高	低	高	高	高
支持外键		支持

8. 浮点数和定点数

名称	数据类型	特点	使用场景
浮点数	float/double	存在误差，不能直接比较两个浮点数，可对数据自动进行四舍五入	对数据精度有一定的容忍度的数据
定点数	decimal	没有误差，对于超出精度的数据可以根据SQLMode进行警告或禁止插入	对货币等精度敏感的数据，应使用定点数

9. MySql的字符集

对于客户端和服务端交互，MySql提供了三个参数 character_set_client(客户端)、character_set_connection(连接)、character_set_results(返回结果)标示字符集，只有当这三个字符集完全相同的时候，才能确保用户写入的数据正确度出，一般在 my.cnf 中用[mysql] default-character-set=UTF-8来设置默认字符集

10. 查看执行计划

-- 使用explain命令查看执行计划，分析索引使用情况，找出优化方案
explain select * from t where c1='a' and c2 like '%b%' \G;

11. 视图的可更新性

在视图中可以进行update和delete操作,操作的结果会反应在视图的源表中.例如:update了视图的一个字段,对应的源表中的字段也会自动修改,delete操作同理。但是，包含以下条件的视图是不能进行更新操作的：
1）视图的SQL中包含聚合函数（sum，count等）、DISTINCT、GROUP BY、HAVING、UNION或UNION ALL
2）常量视图
3）select中包含子查询
4）join
5）from一个不能更新的视图
6）where子句的子查询引用了from子句中的表

12. 锁表和解锁

--加读锁，当前session和其他session还可以继续读取表数据，但是都不能更新表数据
lock tables tableName read;
--加写锁，其他session不能读写表数据，只有当前session可读写表数据
lock tables tableName write;
--解锁，会释放当前线程获得的任何锁定，当与服务器的链接断开时，当前线程的锁定也会被隐含解锁
unlock tables

13. 事务

-- 开始一个事务，开启事务时，unlock tables会被隐含执行
start transaction  或 begin
-- 提交事务 / 提交事务后立即启动一个新的同一隔离级别的事务 / 提交事务后断开客户端连接
commit / commit and chain / commit and release
-- 回滚整个事务
rollback
-- 在事务执行过程过（start transation 后 commit或rollback前）定义一个名字为pointName的point
savepoint pointName
-- 回滚到名字为pointName的point，在point之后的sql会被回滚
rollback to savepoint pointName
-- 设置事务自动提交/不自动提交
set autocommit=1或0

14. 分布式事务（直到MySQL-5.7.7才完美支持分布式事务）

目前Mysql分布式事务的缺点：1）性能太低，与一个数据库的事务相比差了10倍 2）对版本有要求，只有5.7.7以上的版本才有完美的分布式事务解决方案

15. 对于特别大的表，可以使用分区功能（分区对应用来说完全透明，不影响应用业务逻辑）

分区的优点：1）可以存储更多数据 2）优化查询，where子句包含分区字段时，可以只扫描一个或多个分区 3）可以快速删除过期的数据 4）跨多个磁盘来分散查询，能获得更大的吞吐量

分区类型	说明	接收数据类型	多字段
RANGE分区	基于一个给定的连续区间范围，把数据分配到不同的分区	除blob和txt以外的类型	支持
LIST分区	类似RANGE分区，区别在于LIST分区是基于枚举值进行分区，RANGE分区是基于连续的值	INT	不支持
HASH分区	基于给定的分区个数，把数据分配到不同的分区 ，支持自定义表达式	INT	不支持
KEY分区	类似HASH分区，不支持自定义表达式	除blob和txt以为的类型	支持

HASH和KEY分区一般搭配linear关键字强制使用线性分区方式，可以防止重新调整分区的时候导致大量计算的问题。

--查看当前数据库是否支持分区
show plugins
--如果结果里有名称为partition，状态为ACTIVE的数据，表名当前数据库支持分区
-- 1.Hash分区方式（如果有主键，只能使用主键作为分区字段）,Hash分区只支持int。
create table emp (id int, salary decimal(7,2), birth_date DATE) 
engine=innodb 
partition by hash(MONTH(birth_date)) 
partitions 6

--2. Key分区方式，支持使用字符串进行分区。
create table emp (id int, a varchar(10)) 
engine=innodb 
partition by key(a) 
partitions 4

--2.5 Hash和Key分区默认使用取膜的方式进行分区，导致的问题是新增或删除分区的时候，之前的分区都要进行重新计算，所以MySQL提供了一种线性Hash和线性key分区，使用2的幂的运算法则来解决这个问题。
create table emp (id int, a varchar(10)) 
engine=innodb 
partition by linear key(a) 
partitions 4

--3.Range分区方式，按数字大小分段
create table emp1 (id int, salary decimal(7,2), birth_date DATE, primary key(id)) 
engine=innodb 
partition by range(id)(
    partition p0 values less than (10)
        INDEX DIRECTORY = '/data/emp'
        DATA DIRECTORY = '/data/emp',
    partition p1 values less than (20)
        INDEX DIRECTORY = '/data1/emp'
        DATA DIRECTORY = '/data1/emp',
    partition p2 values less than (30)
        INDEX DIRECTORY = '/data2/emp'
        DATA DIRECTORY = '/data2/emp',
    partition p3 values less than maxvalue
        INDEX DIRECTORY = '/data3/emp'
        DATA DIRECTORY = '/data3/emp'
)

--4. list分区，分区字段只能在有限集合内
create table emp3 (id int, salary decimal(7,2), birth_date DATE, category int) 
engine=innodb 
partition by list(id)(
    partition p0 values in (1,3),
    partition p1 values in (2,4),
    partition p2 values in (5,7,9),
    partition p3 values in (10,11)
)

--5. 新增/删除/拆分/合并Range和List分区
alter table emp3 drop partition p2;
alter table emp3 add partition (partition p4 values less than (2030));
alter table emp3 reorganize partition p3 into (
  partition p2 values less than (1000),
  partition p4 values less than (2000)
);
alter table emp3 reorganize partition p2,p3,p4 into( partition p5  values less than (5000)); 

--6. 新增/删除Hash和Key分区
alter table emp add partition 2;  (减少到2个分区)
alter table emp coalesce partition 8;  (新增8个分区)