mysql之重要命令（三）

一、引言

人脑记录的知识，往往会遗忘，还是记录到博客放心点

二、命令

-- 查看版本
show variables like '%version%';
-- 支持哪些存储引擎
show engines;
-- 默认存储引擎
show variables like '%storage_engine%';
-- 默认隔离级别
show variables like 'transaction_isolation';
-- 分析sql执行结果
explain select 1 from dual;
-- 关闭自动提交 set autocommit=0;
-- 开启自动提交 set autocommit=1;
-- 自动提交模式 ON-开启 OFF-关闭
show variables like '%autocommit%';
-- 查找耗时超过1秒的事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>1;
-- 查看数据库
show databases;
-- 切换到某数据库
use javaxiaobang;
-- 查看表结构
show create table order_detail;
-- 查看事务隔离级别 （READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE）
select @@global.tx_isolation,@@tx_isolation;
-- 设置全局事务隔离级别
set global transaction isolation level READ COMMITTED;
-- 设置当前session事务隔离级别
set session transaction isolation level READ COMMITTED;

三、执行效果

mysql> -- 查看版本
mysql> show variables like '%version%';
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.7.22              |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| tls_version             | TLSv1,TLSv1.1       |
| version                 | 5.7.22-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+
8 rows in set (0.00 sec)

mysql> -- 支持哪些存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> -- 默认存储引擎
mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)

mysql> -- 默认隔离级别
mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

mysql> -- 分析sql执行结果
mysql> explain select 1 from dual;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> -- 关闭自动提交 set autocommit=0;
mysql> -- 开启自动提交 set autocommit=1;
mysql> -- 自动提交模式 ON-开启 OFF-关闭
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

mysql> -- 查找耗时超过1秒的事务
mysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>1;
Empty set (0.00 sec)