mysql 函数笔记

1、coalesce  若为空，则取下一个值或字段

用法：select  coalesce(a,b,c)     a,b,c之间可多个

如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

mysql> select name from test1;
+--------+
| name   |
+--------+
| 小明   |
| 小王   |
| 小丽   |
| 小王   |
| 小明   |
| 小明   |
| NULL   |
+--------+
7 rows in set (0.00 sec)


mysql> select coalesce(name,'1') from test1;
+--------------------+
| coalesce(name,'1') |
+--------------------+
| 小明               |
| 小王               |
| 小丽               |
| 小王               |
| 小明               |
| 小明               |
| 1                  |
+--------------------+
7 rows in set (0.00 sec)


mysql> select coalesce(name,null,'11') from test1;
+--------------------------+
| coalesce(name,null,'11') |
+--------------------------+
| 小明                     |
| 小王                     |
| 小丽                     |
| 小王                     |
| 小明                     |
| 小明                     |
| 11                       |
+--------------------------+
7 rows in set (0.00 sec)

mysql> select coalesce(name,null,null,'111') from test1;
+--------------------------------+
| coalesce(name,null,null,'111') |
+--------------------------------+
| 小明                           |
| 小王                           |
| 小丽                           |
| 小王                           |
| 小明                           |
| 小明                           |
| 111                            |
+--------------------------------+
7 rows in set (0.00 sec)

2、with rollup 分组统计数据后再计算汇总

mysql> select name,sum(singin) from test1 group by name;
+--------+-------------+
| name   | sum(singin) |
+--------+-------------+
| 小丽   |           2 |
| 小明   |           7 |
| 小王   |           7 |
+--------+-------------+
3 rows in set (0.00 sec)


mysql> select coalesce(name,'总数'),sum(singin) from test1 group by name with rollup;
+-------------------------+-------------+
| coalesce(name,'总数')   | sum(singin) |
+-------------------------+-------------+
| 小丽                    |           2 |
| 小明                    |           7 |
| 小王                    |           7 |
| 总数                    |          16 |
+-------------------------+-------------+
4 rows in set (0.01 sec)

3、null的比较

当字段的值是null时，只能通过is null 或 is not null来查询，不能用=null 或!=null来查询。

若字段a的值是null，字段b的值是null，那么，a=b是会返回false的。可使用<=>运算符，那么，a<=>b会返回true。

mysql> select distinct a.name from test1 a,test1_copy b where a.name = b.name;
+--------+
| name   |
+--------+
| 小明   |
| 小王   |
+--------+
2 rows in set (0.00 sec)


mysql> select distinct a.name from test1 a,test1_copy b where a.name <=> b.name;
+--------+
| name   |
+--------+
| 小明   |
| 小王   |
| NULL   |
+--------+
3 rows in set (0.00 sec)

4.mysql截取函数

                  left('1234567890',4): 1234
                 right('1234567890',4): 7890
             substring('1234567890',4): 4567890
            substring('1234567890',-4): 7890
           substring('1234567890',4,2): 45
 substring_index('123.456.7890','.',1): 123
substring_index('123.456.7890','.',-1): 7890

5.前后去空格，ltrim去左边空格，rtriim去右边空格

select ltrim('  hello  '),rtrim('  hello  ');
+--------------------+--------------------+
| ltrim('  hello  ') | rtrim('  hello  ') |
+--------------------+--------------------+
| hello              |   hello            |
+--------------------+--------------------+

MySQL online ddl

来源于：http://www.cnblogs.com/cchust/p/4639397.html

在5.6 online ddl推出以前，执行ddl主要有两种方式：copy方式和inplace方式，inplace方式又称为fast index creation。

copy方式：拷贝格式，全程锁表（允许查询），较慢。

        创建索引步骤： 新增带索引的临时表--锁原表--拷贝数据到临时表--rename表，升级字典锁--完成

inplace方式：较快，因为不拷贝数据。但仅支持添加/删除索引两种方式，且需要全程锁表。（全量+增量的方式）

        创建索引步骤：新建索引的数据字典--锁表--读取聚集索引，构造新的索引项，排序并插入新索引--等待打开表的所有只读事务提交--结束

online ddl方式，包含3个阶段，prepare阶段、ddl执行阶段，commit阶段。

包含了copy和inplace方式。

1）不支持online的ddl操作使用copy方式（修改列、删除主键，修改字符集等），这些操作都会导致记录格式发生变化，无法通过简单的全量+增量的方式实现online。

2）对于inplace方式，mysql内部以“是否修改记录格式”为基准分为两类：

A.REBUILD方式：需要重建建表，如optimize table、添加索引、添加/删除列、修改列NULL/NOT NULL属性等；（比NO-REBULID方式多了一个ddl执行阶段）

B.NO-REBUILD方式：只需要修改表的元数据，如删除索引、修改列名、修改列默认值、修改列自增值等。