您当前的位置： 首页 > 学无止境 > 心得笔记 网站首页心得笔记
马哥linux运维学习笔记-MySQL系列之九——MySQL事务和隔离级别
发布时间：2019-03-17 13:50:18编辑：雪饮阅读（）
insert的set用法
mysql> insert into test set id=36;
Query OK, 1 row affected (0.00 sec)
从一张表中获取数据插入到另外一张表中
mysql> insert into test(id) select id from s_user where id>1110;
Query OK, 24 rows affected (0.02 sec)
Records: 24 Duplicates: 0 Warnings: 0
truncate清空表与delete清空表的区别
清空表之前
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 35 | |
| 36 | |
| 1111 | |
| 1112 | |
| 1113 | |
| 1114 | |
| 1115 | |
| 1116 | |
| 1117 | |
| 1118 | |
| 1119 | |
| 1120 | |
| 1121 | |
| 1122 | |
| 1123 | |
| 1124 | |
| 1125 | |
| 1126 | |
| 1127 | |
| 1128 | |
| 1129 | |
| 1130 | |
| 1131 | |
| 1132 | |
| 1133 | |
| 1134 | |
+------+------+
26 rows in set (0.00 sec)
使用delete清空表并插入新数据
mysql> delete from test;
Query OK, 26 rows affected (0.00 sec)
mysql> insert into test(name) values('cxg');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1135 | cxg |
+------+------+
1 row in set (0.00 sec)
使用truncate清空表并插入新数据
mysql> truncate test;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test(name) values('cxg');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | cxg |
+----+------+
1 row in set (0.00 sec)
总结
使用truncate清空表更彻底，而使用delete清空表后新增数据时主键id会依据表之前没有清空时的最大id为基数进行递增。
查看当前时间
mysql> select current_time;
+--------------+
| current_time |
+--------------+
| 20:30:03 |
+--------------+
1 row in set (0.01 sec)
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 20:30:12 |
+----------------+
1 row in set (0.00 sec)
读锁
设置读锁
设置读锁后，数据可读，但不可以插入
mysql> lock tables test read;
Query OK, 0 rows affected (0.00 sec)
当前会话在有读锁的时候进行插入数据
mysql> insert into test(name) values('cxg2');
ERROR 1099 (HY000): Table 'test' was locked with a READ lock and can't be update
而在有读锁的时候在其它会话进行插入数据的时候就会一直不响应，直到读锁解除
解除读锁
mysql> unlock tables;
Query OK, 0 rows affected (0.02 sec)
关键字词：linux,mysql,事务,隔离级别