导读
之前发过一些mysql常用语句; 但有些mysql的语句还不错,虽然不常用,至少我不常用.
DO
功能: 执行表达式,但是没有返回值
语法: 就是do跟上表达式即可
DO expr [, expr] ...作用: 有时候只是希望执行某些函数, 并不关心它的返回,甚至希望其不要返回.
例子:
(root@127.0.0.1) [db1]> select get_lock('AAA',10);
+--------------------+
| get_lock('AAA',10) |
+--------------------+
| 1 |
+--------------------+
1 row in set (0.00 sec)
(root@127.0.0.1) [db1]> do RELEASE_LOCK('AAA');
Query OK, 0 rows affected (0.00 sec)
(root@127.0.0.1) [db1]> do SLEEP(5);
Query OK, 0 rows affected (5.01 sec)TABLE (8.0.19)
没想到吧,这也是个语句
功能: 查询表的所有字段, 同select * from table
语法: 把select * from换成 TABLE, 其它不变(不支持where)
TABLE table_name
[ORDER BY column_name]
[LIMIT number [OFFSET number]]
[INTO OUTFILE 'file_name'
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name] ...]作用: 偷点懒
例子:
(root@127.0.0.1) [db1]> TABLE db1.t1 limit 1;
+------+
| id |
+------+
| 11 |
+------+
1 row in set (0.00 sec)EXCEPT
功能: 第一个查询块不在第二个查询块中的结果集. 和left join where b.k is null差不多,就是第一个查询块独占的.
语法: QUERY_EXP01 EXCEPT QUERY_EXP02
query_expression_body EXCEPT [ALL | DISTINCT] query_expression_body
[EXCEPT [ALL | DISTINCT] query_expression_body]
[...]作用: 偷点懒
例子:
(root@127.0.0.1) [db1]> create table db1.t1(c1 int, c2 int);
Query OK, 0 rows affected (0.01 sec)
(root@127.0.0.1) [db1]> insert into db1.t1 values(1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
(root@127.0.0.1) [db1]> create table db1.t2(c1 int, c2 int);
Query OK, 0 rows affected (0.02 sec)
(root@127.0.0.1) [db1]> insert into db1.t2 values(2,2);
Query OK, 1 row affected (0.01 sec)
(root@127.0.0.1) [db1]> TABLE t1 EXCEPT TABLE t2;
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
| 3 | 3 |
+------+------+
2 rows in set (0.00 sec)INTERSECT
既然有差集,那也就有交集了哦
功能: 两表的交集
语法: QUERY_EXP01 INTERSECT QUERY_EXP02
query_expression_body INTERSECT [ALL | DISTINCT] query_expression_body
[INTERSECT [ALL | DISTINCT] query_expression_body]
[...]例子:
(root@127.0.0.1) [db1]> TABLE t1 INTERSECT TABLE t2;
+------+------+
| c1 | c2 |
+------+------+
| 2 | 2 |
+------+------+
1 row in set (0.00 sec)HANDLER
功能: HANDLER(憨豆)可以直接通过存储引擎访问数据,这速度就很快了
语法: 打开表,读数据,关闭表
HANDLER tbl_name OPEN [ [AS] alias]
HANDLER tbl_name READ index_name { = | <= | >= | < | > } (value1,value2,...)
[ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST }
[ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ { FIRST | NEXT }
[ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name CLOSE例子:
(root@127.0.0.1) [db1]> HANDLER t1 OPEN;
Query OK, 0 rows affected (0.00 sec)
(root@127.0.0.1) [db1]> HANDLER t1 READ NEXT;
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.01 sec)
(root@127.0.0.1) [db1]> HANDLER t1 READ NEXT;
+------+------+
| c1 | c2 |
+------+------+
| 2 | 2 |
+------+------+
1 row in set (0.00 sec)
(root@127.0.0.1) [db1]> HANDLER t1 CLOSE;
Query OK, 0 rows affected (0.00 sec)
(root@127.0.0.1) [db1]> VALUES
功能: 构造一个表,有时候不想create的时候就可以使用这种方式来构建表
语法: VALUES ROW(c1,c2), ROW(c1,c2)
VALUES row_constructor_list [ORDER BY column_designator] [LIMIT number]
row_constructor_list:
ROW(value_list)[, ROW(value_list)][, ...]
value_list:
value[, value][, ...]
column_designator:
column_index例子:
(root@127.0.0.1) [db1]> VALUES ROW(1,2), ROW(3,4) except VALUES ROW(1,2);
+----------+----------+
| column_0 | column_1 |
+----------+----------+
| 3 | 4 |
+----------+----------+
1 row in set (0.00 sec)RESTART
上一章我们讲了mysql的信号量处理, 里面有关SIGUSR2是表示重启的,而mysql本身就支持重启命令(8.0), 原理也是退出的时候设置退出状态码为16, 然后mysqld_safe之类的根据退出状态码进行启动
逻辑可参考如下
export MYSQLD_RESTART_EXIT=16
while true ; do
bin/mysqld mysqld options here
if [ $? -ne $MYSQLD_RESTART_EXIT ]; then
break
fi
done例子:
(root@127.0.0.1) [db1]> show global status like 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 3039 |
+---------------+-------+
1 row in set (0.00 sec)
(root@127.0.0.1) [db1]> restart;
Query OK, 0 rows affected (0.01 sec)
(root@127.0.0.1) [db1]> show global status like 'uptime';
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: 9
Current database: db1
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 11 |
+---------------+-------+
1 row in set (0.03 sec)参考:
https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html
真的涨知识了！之前只熟悉MySQL的常用语句，完全没了解过DO这种无需返回值的用法，看了释放锁、睡眠的例子才发现，在只需要执行操作不关心结果的场景里，它简直太实用了，省去了处理返回结果的麻烦。感谢作者分享这么实用的冷知识，期待能看到更多这类好用的MySQL干货！