前置：本人测试使用的系统为windows，MySQL版本为5.7+，以下结果可能会受到影响，这里提前说明下。

表结构：

CREATE TABLE `ifnull_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `money1` decimal(10,2) DEFAULT NULL,
  `money2` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

表数据：

假设我要查询money1大于money2的，并且要把差值计算出来。SQL如下：

SELECT ( money1 - money2 ) AS money,money1,money2 FROM ifnull_test WHERE money1 > money2

但当表中数据存在null的情况下，不管是比较还是计算都会出问题，上面SQL的运行结果为：

比我们预想的要差很多，按照我们的思维，表中的三条数据都是应该出现在结果中，这就需要用到IFNULL这个SQL函数了：

-- IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。
IFNULL(expression, alt_value)

这时我们就可以加上这个函数完善上面的查询语句：

SELECT ( IFNULL(money1,0) - IFNULL(money2,0) ) AS money,money1,money2 FROM ifnull_test WHERE IFNULL(money1,0) > IFNULL(money2,0)

查询结果：

end！

产生原因

前置：本人测试使用的系统为windows，MySQL版本为5.7+，以下结果可能会受到影响，这里提前说明下。

下载安装的是最新版的mysql5.7.x版本，默认是开启了 only_full_group_by 模式的，但开启这个模式后，原先的 group by 语句就报错，然后又把它移除了。

一旦开启 only_full_group_by ，感觉，group by 将变成和 distinct 一样，只能获取受到其影响的字段信息，无法和其他未受其影响的字段共存，这样，group by 的功能将变得十分狭窄了

only_full_group_by 模式开启比较好。

因为在 mysql 中有一个函数： any_value(field) 允许，非分组字段的出现（和关闭 only_full_group_by 模式有相同效果）。

解决办法

所以解决办法大致有两种：

第一种：

1、查看sql_mode

select @@global.sql_mode;

查询出来的值一般为：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

2、去掉ONLY_FULL_GROUP_BY，重新设置值。

set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

3、上面是改变了全局sql_mode，对于新建的数据库有效。对于已存在的数据库，则需要在对应的数据下执行：

set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

第二种：

在sql查询语句中不需要group by的字段上使用any_value()函数。

这种对于已经开发了不少功能的项目不太合适，毕竟要把原来的sql都给修改一遍

CAST(expression AS TYPE) 函数可以将任何类型的值转换为具有指定类型的值,利用该函数可以直接在数据库层处理部分因数据类型引起的问题。

以下为该函数支持的数据类型：

示例：

固定字符串转为SIGNED int 型

SELECT CAST("12321" AS SIGNED  ) AS result 
运行结果：12321

ALL父查询中的每个结果大于子查询结果集中的每一个值，则为真。

//查询表table_name1中age字段大于table_name2所有的age的数据
select * from table_name1 where age(select age from table_name2)

ANY父查询中的每个结果大于子查询结果集中的任意一个值，则为真。

//查询表table_name1中age字段大于table_name2中任意一个age的值
select * from table_name1 where age(select age from table_name2)

SOME作用大致与ANY相同，日常使用中看做一致即可。

SQL优化时我们提到，很多时候用 exists 代替 in 是一个好的选择，这里明确说一下exists的用法和作用。

语法：

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

意义：

• EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。

注意：

• EXISTS子句中不允许有COMPUTE子句和INTO关键字

• EXISTS中column_name实际上是不影响查询结果的，所以为了更快地检索，建议只写一个主键字段，另外EXISTS中where子句要和外面的表有逻辑关系，否则要不查询所有数据，要不一行数据都不出现。

用法：

• 在子查询中使用NULL仍然返回结果集

select * from TableIn where exists(select null)
select * from TableIn//与上方效果相同

• 比较使用EXISTS和IN的查询的使用，返回结果相同

select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)
select * from TableIn where ANAME in(select BNAME from TableEx比较使用EXISTS和)

• 比较使用 EXISTS 和 ANY 的查询。注意两个查询返回相同的结果。

select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)
select * from TableIn where ANAME=ANY(select BNAME from TableEx)

作用：

mysql union操作符用于连接两个以上的select语句的结果组合到一个结果集合中。

语法：

SELECT column_name1, column_name2, ... column_namen
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT column_name1, column_name2, ... column_namen
FROM tables
[WHERE conditions];

注意：

• 使用时select查询的column_name字段数量和内容要一致

• DISTINCT: 默认值，可不写，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。

• ALL：可选，返回所有结果集，包含重复数据。

SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ORDER BY 列名称；
SELECT 列名称 FROM 表名称 UNION ALL SELECT 列名称 FROM 表名称 ORDER BY 列名称；

前提条件

• MySQL版本必须大于5.7.6，小于这个版本只支持英文全文索引，不支持中文，需要利用分词器把中文段落预处理拆分成单词，然后存入数据库。

• 前置条件需要用到FULLTEXT索引，所以数据表引擎必须使用MyISAM，建立全文检索的字段类型必须是char,varchar,text。

• 对于大的数据库，将数据装载到一个没有 FULLTEXT 索引的表中，然后再使用 ALTER TABLE   (或 CREATE INDEX) 创建索引，这将是非常快的。而将数据装载到一个已经有 FULLTEXT 索引的表中，是非常慢的。

• MySQL 中使用全局变量ngram_token_size来配置ngram中n的大小，它的取值范围是1到10，默认值是2。通常ngram_token_size设置为要查询的单词的最小字数。如果需要搜索单字，就要把ngram_token_size设置为1。在默认值是2的情况下，搜索单字是得不到任何结果的。因为中文单词最少是两个汉字，推荐使用默认值2。

• 索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）。

• 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。

• 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）。

• 忽略词中的单引号。例如，don't索引为dont。

• 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果。

全文本搜索

进行全文本搜索

SELECT note_text 
FROM table_name
WHERE Match(note_text) Against('search_text')

• Match()指定被搜索的列，Against()指定要使用的搜索表达式。

• 使用完整的 Match() 说明传递给 Match() 的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。

• 搜索不区分大小写 除非使用BINARY方式，否则全文本搜索不区分大小写。

进行全文本搜索后匹配等级

select note_text,match(note_text) AGAINST('search_text')AS rank 
FROM table_name;

• Match()和Against()用来建立一个计算列（别名为rank），此列包含全文本搜索计算出的等级值。等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来。不包含词search_text的行等级为0（因此不被前一例子中WHERE子句选择）。确实包含词search_text的每行都有一个等级值，文本中词靠前的行的等级值比词靠后的行的等级值高。

• 排序多个搜索项，如果指定多个搜索项，则包含多数匹配词的那些行将具有比包含较少词（或仅有一个匹配）的那些行高的等级值。

进行全文本搜索使用查询扩展

SELECT note_text 
FROM table_name
Match(note_text) Against('search_text' WITH QUERY EXPANSION);

例如：

SELECT note_text FROM productnotes Match(note_text) Against('anvils' WITH QUERY EXPANSION);

结果：

Multiple customer returns，anvils failing to drop fast enough orfal7ing backwards on purchaser. Recommend that customer considersusing heavier anvils.
Customer complaint: Sticks not individually wrapped，too easy tomistakenly detonate all at once. Recommend individua7 wrapping.Customer complaint: Not heavy enough to generate flying starsaround headof victim. If being purchased for dropping，recommendANVO2 or ANVO3 instead.
Please note that no returns wil7 be accepted if safe opened usingexplosives .
Customer complaint: rabbit has been able to detect trap，foodapparently less effective now .
Customer complaint: Circular hole in safe floor can apparently beeasily cut with handsaw.
Matches not included，recommend purchase of matches or detonator(item DTNTR).

• 这次返回了7行。第一行包含词anvils，因此等级最高。第二行与anvils无关，但因为它包含第一行中的两个词（customer和recommend），所以也被检索出来。第3行也包含这两个相同的词，但它们在文本中的位置更靠后且分开得更远，因此也包含这一行，但等级为第三。第三行确实也没有涉及anvils（按它们的产品名）。

• 正如所见，查询扩展极大地增加了返回的行数，但这样做也增加了你实际上并不想要的行的数目。

布尔文本搜索

SELECT note_text 
FROM table_name
WHERE Match(note_text) Against('表达式' IN BOOLEAN MODE)

例如：匹配note_text中带有shx的，且不包含big的。

SELECT note_text 
FROM table_name
WHERE Match(note_text) Against('+shx -big' IN BOOLEAN MODE)

全文本布尔操作符

索引说明

• MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

• 拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

• 索引分单列索引和组合索引：

  单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。

  组合索引，即一个索引包含多个列。

• 创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

• 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

• 上面都在说使用索引的好处，但过多的使用索引将会造成滥用。

  因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

• 建立索引会占用磁盘空间的索引文件。

索引类型

INDEX(普通索引)

普通索引是mysql中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。

普通索引允许在定义索引的列中插入重复值和空值

创建普通索引时，通常使用的关键字是index或key

SQL语句添加方式：

CREATE INDEX indexName ON table_name (column_name)//新建
ALTER table tableName ADD INDEX indexName(columnName)//修改表结构添加普通索引

UNIQUE(唯一索引)

唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。

唯一索引列的值必须唯一，允许有空值。

如果是组合索引，则列值的组合必须唯一。

创建唯一索引通常使用 unique 关键字。

SQL语句添加方式：

CREATE UNIQUE INDEX indexName ON table_name (column_name(length)) //新建
ALTER table mytable ADD UNIQUE [indexName] (column_name(length))//修改表结构添加唯一索引

FULLTEXT(全文索引)

全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。

在 MySQL 中只有 MyISAM 存储引擎支持全文索引

全文索引允许在索引列中插入重复值和空值。

不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。

创建全文索引使用 FULLTEXT 关键字。

CREATE FULLTEXT INDEX index_name ON table_name(column_name);

SPATIAL(空间索引)

空间索引是对空间数据类型的字段建立的索引，使用 SPATIAL 关键字进行扩展。

创建空间索引的列必须将其声明为 NOT NULL，空间索引只能在存储引擎为 MyISAM 的表中创建。

空间索引主要用于地理空间数据类型 GEOMETRY。对于初学者来说，这类索引很少会用到。

SQL语句添加方式：

CREATE SPATIAL INDEX index_name ON table_name(column_name);

PRIMARY KEY(主键索引)

顾名思义，主键索引就是专门为主键字段创建的索引，也属于索引的一种。

主键索引是一种特殊的唯一索引，不允许值重复或者值为空。

创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

索引实际使用区分

索引在逻辑上分为以上 5 类，但在实际使用中，索引通常被创建成单列索引和组合索引。

单列索引

单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。

单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。

只要保证该索引只对应一个字段即可。

多列索引

组合索引也称为复合索引或多列索引。

相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引。

该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。

但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

例如：在表中的 id、name 和 sex 字段上建立一个多列索引，那么，只有查询条件使用了 id 字段时，该索引才会被使用。

提示

一个表可以有多个单列索引，但这些索引不是组合索引。

一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。

比如：在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。

concat（）函数

 作用：

函数用于将多个字符串连接成一个字符串。

返回值：

返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

SELECT CONCAT('My', 'S', 'QL');
-> 'MySQL'
SELECT CONCAT('My', NULL, 'QL');//只要有一个为null，则输出null
NULL
SELECT CONCAT(14.3);
'14.3'

concat_ws（）函数

 作用：

函数用于将多个字符串连接成一个字符串。

返回值：

返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

参数说明：

第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。

SELECT CONCAT_WS(',','First name','Second name','Last Name');
'First name,Second name,Last Name'
SELECT CONCAT_WS(',','First name',NULL,'Last Name');//null会直接忽略
'First name,Last Name'
SELECT CONCAT_WS(',','First name','','Last Name');//空是可以正常连接的
'First name,,Last Name'

group_concat（）函数

语法说明

group_concat函数的作用是将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

(1) 基本语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc  ] [separator '分隔符'] )；

(2) 说明：可以使用distinct排除重复值；可以使用order by子句对结果中的值进行排序。

(3) 返回的字符串的默认分隔符从逗号(，)，若要改为其他分隔符，则使用SEPARATOR子句修改分隔符。

举例

select user_id,GROUP_CONCAT(`like_name` order by `create_time` desc SEPARATOR ',' )
FROM user_table  
WHERE user_id > 10 GROUP BY user_id

上方SQL对应的情况举例：

假设表结构为id（主键）、user_id（用户id）、like_name（爱好）、create_time（创建时间），想一次性查询出每个人的所有爱好（每条记录代表一个人），不用二次数据处理。

• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

• 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

• 应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

• 应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10 union all select id from t where num=20

• in 和 not in 也要慎用，否则会导致全表扫描。如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

• 下面的查询也将导致全表扫描：

select id from t where name like ‘%李%’

若要提高效率，可以考虑全文检索。

• 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。

如下面语句将进行全表扫描：

select id from t where num=@num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num

• 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为：

select id from t where num=100*2

• 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)=’abc’

name以abc开头的id应改为：

select id from t where name like ‘abc%’

• 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

• 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

• 不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table #t(…)

• 很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

• 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

• 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

• 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

• 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

• 尽可能的使用 char/nchar代替varchar/nvarchar ，因为首先固定长度的char/nchar比可变长度的varchar/nvarchar 存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

• 任何地方查询都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

• 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

• 避免频繁创建和删除临时表，以减少系统表资源的消耗。

• 临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

• 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

• 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

• 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

• 使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

• 与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

• 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC 消息。

• 尽量避免大事务操作，提高系统并发能力。

• 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。