Mysql系统信息函数

桃扇骨 2021-07-24 20:00 354阅读 0赞

**一 查看当前MySQL版本号**

mysql> SELECT VERSION();
    +-----------+
    | VERSION() |
    +-----------+
    | 5.7.22    |
    +-----------+
    1 row in set (0.00 sec)

**二 查看当前用户的连接数**

mysql> SELECT CONNECTION_ID();
    +-----------------+
    | CONNECTION_ID() |
    +-----------------+
    |               2 |
    +-----------------+
    1 row in set (0.00 sec)

**三 使用SHOW PROCESSLIST命令输出当前用户的连接信息**

**四 查看当前使用的数据库**

**五 获取当前登录用户名称**

**六 使用CHARSET()函数返回字符串使用的字符集**

SELECT CHARSET('abc'),
         CHARSET(CONVERT('abc' USING latin1)),
         CHARSET(VERSION());
    +----------------+--------------------------------------+--------------------+
    | CHARSET('abc') | CHARSET(CONVERT('abc' USING latin1)) | CHARSET(VERSION()) |
    +----------------+--------------------------------------+--------------------+
    | utf8           | latin1                               | utf8               |
    +----------------+--------------------------------------+--------------------+
    1 row in set (0.00 sec)

**七 使用COLLATION()函数返回字符串排列方式**

mysql> SELECT COLLATION('abc'),COLLATION(CONVERT('abc' USING utf8));
    +------------------+--------------------------------------+
    | COLLATION('abc') | COLLATION(CONVERT('abc' USING utf8)) |
    +------------------+--------------------------------------+
    | utf8_general_ci  | utf8_general_ci                      |
    +------------------+--------------------------------------+
    1 row in set (0.00 sec)

参考： [https://www.cnblogs.com/EasonJim/p/8128196.html][https_www.cnblogs.com_EasonJim_p_8128196.html]

**八 使用SELECT LAST\_INSERT\_ID查看最后一个自动生成的列值**

1．一次插入一条记录

首先创建表worker，其Id字段带有AUTO\_INCREMENT约束

CREATE TABLE worker (Id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
          Name VARCHAR(30));
    Query OK, 0 rows affected (0.23 sec)

分别单独向表worker中插入2条记录：

mysql> INSERT INTO worker VALUES(NULL, 'jimy');
    Query OK, 1 row affected (0.03 sec)
    
    mysql> INSERT INTO worker VALUES(NULL, 'Tom');
    Query OK, 1 row affected (0.02 sec)
    
    mysql> SELECT * FROM worker;
    +----+------+
    | Id | Name |
    +----+------+
    |  1 | jimy |
    |  2 | Tom  |
    +----+------+
    2 rows in set (0.00 sec)

查看已经插入的数据可以发现，最后一条插入的记录的Id字段值为2，使用LAST\_INSERT\_ID()查看最后自动生成的Id值：

mysql> SELECT LAST_INSERT_ID();
    +------------------+
    | LAST_INSERT_ID() |
    +------------------+
    |                2 |
    +------------------+
    1 row in set (0.01 sec)

2．一次同时插入多条记录

接下来，向表中插入多条记录

INSERT INTO worker VALUES
         (NULL, 'Kevin'),(NULL,'Michal'),(NULL,'Nick');
    Query OK, 3 rows affected (0.03 sec)
    Records: 3  Duplicates: 0  Warnings: 0

查询已经插入的的记录，

mysql> SELECT * FROM worker;
    +----+--------+
    | Id | Name   |
    +----+--------+
    |  1 | jimy   |
    |  2 | Tom    |
    |  3 | Kevin  |
    |  4 | Michal |
    |  5 | Nick   |
    +----+--------+
    5 rows in set (0.00 sec)
    
    
    mysql> SELECT LAST_INSERT_ID();
    +------------------+
    | LAST_INSERT_ID() |
    +------------------+
    |                3 |
    +------------------+
    1 row in set (0.00 sec)

[https_www.cnblogs.com_EasonJim_p_8128196.html]: https://www.cnblogs.com/EasonJim/p/8128196.html