基本使用

SQL 模式

• STRICT_ALL_TABLES 和 STRICT_TRANS_TABLES：
• TRADITIONAL
• ANSI_QUOTES
  • 告诉服务器把双引号是识别为标识符引用字符
• PIPES_AS_CONCAT
  • 将 || 当做连接运算符
• ANSI
  • 启动多种组合模式

设置模式：

mysql ... --sql-model="xxx"

SET sql_mode='xx'SET GLOBAL sql_mode='xx' -- 全局模式

标识符与命名

不加双引号的标识符：

大小写字母 数字 美元符号 下划线组成 可以以数字开头 没有引号的命名不能全由数字构成

如果启用了 IGNORE_SPACE 模式 函数名会变成保留字

标识符的最大长度为64个字符 别名最大长度256字符

完全限定表名与完全限定列名：db.table.column

大小写规则

• SQL 关键字与和函数名，存储过程的名字不区分大小写
• 数据库名 表名 视图名区分大小写取决于操作系统
• 列名、索引名不区分
• 别名区分
• 字符串区分大小写取决于是否是二进制串

为避免出现问题，统一采用小写

字符集与校对规则

字符集指的是一种从二进制编码到某类字符符号的映射。校对规则则是指某种字符集下的排序规则。MySQL中每一种字符集都会对应一系列的校对规则

MySQL采用的是类似继承的方式指定字符集的默认值，每个数据库以及每张数据表都有自己的默认值，他们逐层继承。比如：某个库中所有表的默认字符集将是该数据库所指定的字符集（这些表在没有指定字符集的情况下，才会采用默认字符集）

SHOW CHARSET -- 列出可用字符集

unicode支持：utf8 utf8mb4

数据库操作

USE db; -- 切换数据库CREATE DATABASE [IF NOT EXISTS] db [CHARSET xx] [COLLATE xxx]; -- 创建数据库DROP DATABASE db; -- 删除数据库ALTER DATABASE db [CHARSET xx] [COLLATE xxx]; -- 更改字符集与排序规则

表操作

CREATE TABLE [IF NOT EXISTS] (..) ENGINE = xxx DEFAULT CHARSET = xxx; -- 创建ALTER TABLE (..) ENGINE = xxx DEFAULT CHARSET = xxx; -- 修改CREATE TEMPORARY TABLE table (...); -- 临时表 会话关闭丢失CREATE TABLE table LIKE xxx -- 复制其他表的结构CREATE TABLE table SELECT ... -- 复制查询的数据 一些列的属性不会被复制CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)PARTITION BY RANGE( YEAR(purchased) ) (    PARTITION p0 VALUES LESS THAN (1990),    PARTITION p1 VALUES LESS THAN (1995),    PARTITION p2 VALUES LESS THAN (2000)); -- 分区表DROP TABLE [IF EXISTS] tb; -- 删除表ALTER TABLE tb CHANGE cname cname INT; -- 改变列名并修改列类型ALTER TABLE tb ENGINE = xxx; -- 更改存储引擎ALTER TABLE tb RENAME TO tb1; -- 表重命名

索引

除了 PRIMARY KEY ,绝大部分索引可以使用CREATE IDNEX创建

CREATE UNIQUE INDEX name ON table (...); -- 唯一索引 单列中不允许有重复值出现CREATE FULLTEXT INDEX name ON table (...); -- 全文索引CREATE SPATIAL INDEX name ON table (...); -- myisam 空间索引CREATE TABLE (  ...  INDEX (name(10)) -- 对字符串前10个字符简历索引)DROP INDEX name ON tb; --删除索引

获取数据库元数据

show语句：

可以使用 LIKE 'xx' 限制输出范围

SHOW DATABASES;SHOW CREATE DATABASE db; -- 显示创建数据库时的语句SHOW CREATE TABLE tb;SHOW TABLES;SHOW TABLE STATUS;SHOW COLUMNS FROM tb;SHOW INDEX FROM tb;

INFORMATION_SCHEMA 库

事务处理

默认是开启执行SQL自动提交的

SET AUTOCOMMIT = 0; -- 关闭自动提交START TRANSACTION; -- 开启事务INSERT INTO person SET name = 'cxk'; -- 执行事务操作COMMIT; -- 提交事务-- ROLLBACK; 回滚事务

事务保存点

START TRANSACTION;insert into person SET name = 'cxkk';SAVEPOINT myp; -- 保存一个事务点insert into person SET name = 'cxkd';ROLLBACK TO SAVEPOINT myp; -- 回滚到事务保存点COMMIT;

事务隔离

设置隔离级别：SET [GLOBAL, SESSION] TRANSACTION ISOLATION LEVEL

在同一个事务中混合使用不同的存储引擎是不可靠的，有的存储引擎不支持事务

隔离级别的选择：

• 上松下严：应用层来适配数据库的隔离级别
• 当Spring开启了事务并设置了传播机制，那么会覆盖Mysql已有的事务隔离级别。如果Mysql不支持该隔离级别，Spring的事务就也不会生效。
• RR （可重复读）RD（读已提交） 搭配使用

外键

CREATE TABLE tb(  ..  FOREIGN KEY (id) REFERENCES parent (id)    ON DELETE CASCADE -- 删除父表相关行 子表也会被删除    ON UPDATE CASCADE -- 更新相关    -- ON DELETE SET NULL 删除时将相关列设置NULL)

全文搜索

• 自然语言搜索
  • 搜索字符串会被解析成单词进行搜索
• 布尔模式搜索
  • 搜索出现某些单词的行
• 查询扩展搜索
  • 先进行自然语言搜索
  • 第二阶段将搜索字符串与第一阶段的结果进行拼接后搜索

特点：

• 需要建立全文索引
• 会忽略常见词
  • 至少在一半的行都出现过的词
• 忽略内置常用词
  • after the
• 忽略过短的单词

使用：

CREATE FULLTEXT INDEX index_actor_info ON actor_test(film_info); -- 创建相关全文索引select * from actor_test where match(film_info) against('LUKE'); -- 自然语言搜索select * from actor_test where match(film_info) against('LUKE CHISUM' IN BOOLEAN MODE); -- 布尔模式select * from actor_test where match(film_info) against('LUKE CHISUM' WITH QUERY EXPANSION); -- 查询扩展

配置：

ft_min_word_len=3 # 最小单词长度