MySQL的函数和运算符 - 信息函数（3）

林员外聊编程 2021-08-01

317

● ICU_VERSION()

用于支持正则表达式操作的 Unicode 国际组件(International Components for Unicode, ICU)库的版本。此功能主要用于测试用例。

● LAST_INSERT_ID(), LAST_INSERT_ID(expr)

如果没有参数，LAST_INSERT_ID() 返回一个 BIGINT UNSIGNED (64位)值，表示作为最近第一个执行的 INSERT 语句成功插入 AUTO_INCREMENT 列自动生成的值。如果没有成功插入行，LAST_INSERT_ID() 的值保持不变。

带一个参数，LAST_INSERT_ID() 返回一个无符号整数。

例如，插入一个生成 AUTO_INCREMENT 值的行后，可以这样得到这个值：

mysql> SELECT LAST_INSERT_ID();
        -> 195

当前正在执行的语句不会影响 LAST_INSERT_ID() 的值。假设使用一条语句生成一个 AUTO_INCREMENT 值，然后在一个多行 INSERT 语句中引用 LAST_INSERT_ID()，该语句将行插入到具有自己的 AUTO_INCREMENT 列的表中。LAST_INSERT_ID() 的值在第二条语句中保持稳定，第二行和后面的行的值不受前面行插入的影响。(应该知道，如果混合了对 LAST_INSERT_ID() 和 LAST_INSERT_ID(expr) 的引用，其效果是不确定的。)

如果前面的语句返回错误，则 LAST_INSERT_ID() 的值是未定义的。对于事务性表，如果由于错误而回滚语句，则 LAST_INSERT_ID() 的值未定义。对于手动 ROLLBACK, LAST_INSERT_ID() 的值不会恢复到事务之前的值，它仍然保持在 ROLLBACK 点的状态。

在存储例程(过程或函数)或触发器的主体内，LAST_INSERT_ID() 的值的变化方式与在这类对象的主体外执行的语句相同。存储例程或触发器对以下语句的 LAST_INSERT_ID() 值的影响取决于例程的类型：

■ 如果存储过程执行了更改 LAST_INSERT_ID() 值的语句，则更改后的值对该过程调用后的语句是可见的。

■ 对于更改值的存储函数和触发器，该值在函数或触发器结束时恢复，因此后面的语句看不到更改的值。

生成的 ID 在服务器中基于每个连接的基础上维护。这意味着函数返回给给定客户端的值是该客户端为影响 AUTO_INCREMENT 列的最近语句一次生成的 AUTO_INCREMENT 值。这个值不受其他客户端的影响，即使它们也生成自己的 AUTO_INCREMENT 值。这种行为确保每个客户端可以检索自己的 ID，而不关心其他客户端的活动，也不需要锁或事务。

如果将一行的 AUTO_INCREMENT 列设置为非“魔幻”值(即非 NULL 和非 0 的值)，则 LAST_INSERT_ID() 的值不会更改。

重要

如果使用单个 INSERT 语句插入多行，LAST_INSERT_ID() 只返回插入的第一行生成的值。这样做的原因是，可以在其他服务器上轻松地复制相同的 INSERT 语句。

例如：

mysql> USE test;


mysql> CREATE TABLE t (
       id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
       name VARCHAR(10) NOT NULL
       );


mysql> INSERT INTO t VALUES (NULL, 'Bob');


mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
|  1 | Bob  |
+----+------+


mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+


mysql> INSERT INTO t VALUES
       (NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa');


mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
|  1 | Bob  |
|  2 | Mary |
|  3 | Jane |
|  4 | Lisa |
+----+------+


mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+

尽管第二个 INSERT 语句在 t 中插入了3行，但是为这3行中的第一行生成的 ID 是 2，后面的 SELECT 语句中 LAST_INSERT_ID() 返回的就是这个值。

如果使用 INSERT IGNORE 则忽略该行，则 LAST_INSERT_ID() 将保持当前值不变(如果连接尚未成功执行 INSERT 操作，则返回 0)，而且对于非事务性表，AUTO_INCREMENT 计数器不会增加。对于 InnoDB 表，当 innodb_autoinc_lock_mode 设置为1或2时，AUTO_INCREMENT 计数器会增加，示例如下：

mysql> USE test;


mysql> SELECT @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
|                          1 |
+----------------------------+


mysql> CREATE TABLE `t` (
       `id` INT(11) NOT NULL AUTO_INCREMENT,
       `val` INT(11) DEFAULT NULL,
       PRIMARY KEY (`id`),
       UNIQUE KEY `i1` (`val`)
       ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


# Insert two rows


mysql> INSERT INTO t (val) VALUES (1),(2);


# With auto_increment_offset=1, the inserted rows
# result in an AUTO_INCREMENT value of 3


mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `i1` (`val`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1


# LAST_INSERT_ID() returns the first automatically generated
# value that is successfully inserted for the AUTO_INCREMENT column


mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+


# The attempted insertion of duplicate rows fail but errors are ignored


mysql> INSERT IGNORE INTO t (val) VALUES (1),(2);
Query OK, 0 rows affected (0.00 sec)
Records: 2  Duplicates: 2  Warnings: 0


# With innodb_autoinc_lock_mode=1, the AUTO_INCREMENT counter
# is incremented for the ignored rows


mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `i1` (`val`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1


# The LAST_INSERT_ID is unchanged because the previous insert was unsuccessful


mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

如果 expr 作为 LAST_INSERT_ID() 的参数给出，则函数返回参数的值，并作为 LAST_INSERT_ID() 返回的下一个值。这可以用来模拟序列：

a. 创建一个表来保存序列计数器并初始化它：

mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);

b. 使用表如此来生成序列号：

mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT LAST_INSERT_ID();

UPDATE 语句增加序列计数器，并导致对 LAST_INSERT_ID() 的下一次调用返回更新后的值。SELECT 语句检索该值。也可以使用 mysql_insert_id() C API 函数来获取该值。

可以在不调用 LAST_INSERT_ID() 的情况下生成序列，但是以这种方式使用该函数的好处是，ID 值在服务器中作为最后一个自动生成的值进行维护。它是多用户安全的，因为多个客户端可以执行 UPDATE 语句并使用 SELECT 语句(或 mysql_insert_id())获得自己的序列值，而不受其他生成自己序列值的客户端的影响，也不会影响其他客户端。

注意，mysql_insert_id() 只在 INSERT 和 UPDATE 语句之后更新，所以在执行其他 SQL 语句(如 SELECT 或 SET)之后，不能使用 C API 函数检索 LAST_INSERT_ID(expr) 的值。

● ROLES_GRAPHML()

返回 utf8 字符串，其中包含一个表示内存角色子图的 GraphML 文档。要查看 <graphml> 元素的内容，需要 ROLE_ADMIN 权限(或已弃用的 SUPER 权限)。否则，结果只显示一个空元素:

mysql> SELECT ROLES_GRAPHML();
+---------------------------------------------------+
| ROLES_GRAPHML()                                   |
+---------------------------------------------------+
| <?xml version="1.0" encoding="UTF-8"?><graphml /> |
+---------------------------------------------------+

● ROW_COUNT()

ROW_COUNT() 返回如下值：

■ DDL 语句：0。这适用于 CREATE TABLE 或 DROP TABLE 等语句。

■ 除 SELECT 之外的 DML 语句：受影响的行数。这适用于像 UPDATE、INSERT 或 DELETE 这样的语句(和以前一样)，但现在也适用于像 ALTER TABLE 和 LOAD DATA 这样的语句。

■ SELECT：如果语句返回结果集，则返回 -1，如果没有返回结果集，则返回“受影响”的行数。例如，对于 SELECT * FROM t1, ROW_COUNT() 返回 -1。对于 SELECT * FROM t1 INTO OUTFILE 'file_name'， ROW_COUNT() 返回写入文件的行数。

■ SIGNAL 语句：0。

对于 UPDATE 语句，默认情况下，受影响行数值是实际更改的行数。如果连接到 mysqld 时，对 mysql_real_connect() 指定 CLIENT_FOUND_ROWS 标志，受影响行值是“发现”的行数，即由 WHERE 子句匹配的行数。

对于 REPLACE 语句，如果新行替换了旧行，则受影响行值为 2，因为在这种情况下，在删除重复的行之后插入了一行。

对于 INSERT ... ON DUPLICATE KEY UPDATE 语句中，如果将行作为新行插入，则每行受影响行值为 1，如果更新现有行则为 2，如果将现有行设置为当前值则为 0。如果指定 CLIENT_FOUND_ROWS 标志，如果将现有行设置为其当前值，则受影响行值为 1(不是 0)。

ROW_COUNT() 值类似于 mysql_affected_rows() C API 函数的值，以及 mysql 客户端在执行语句后显示的行数。

mysql> INSERT INTO t VALUES(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)


mysql> DELETE FROM t WHERE i IN(1,2);
Query OK, 2 rows affected (0.00 sec)


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

重要

对于使用基于语句的复制，ROW_COUNT() 是不可靠的。对此函数会自动使用基于行的复制。

● SCHEMA()

此函数是 DATABASE() 的同义词。

● SESSION_USER()

SESSION_USER() 是 USER() 的同义词。

● SYSTEM_USER()

SYSTEM_USER() 是 USER() 的同义词。

注意

SYSTEM_USER() 函数不同于 SYSTEM_USER 权限。前者返回当前 MySQL 帐户名。后者区分系统用户和普通用户的帐户类别。

● USER()

以 utf8 字符集字符串形式返回当前 MySQL 用户名和主机名。

mysql> SELECT USER();
        -> 'davida@localhost'

该值指示连接到服务器时指定的用户名，以及所连接的客户端主机。该值可以与 CURRENT_USER() 不同。

● VERSION()

返回一个表示 MySQL 服务器版本的字符串。该字符串使用 utf8 字符集。除了版本号之外，该值可能还有一个后缀。

这个函数对于基于语句的复制是不安全的。如果 binlog_format 设置为 STATEMENT 时使用此函数，则会记录一个警告。

mysql> SELECT VERSION();
        -> '8.0.26-standard'

官方文档：

https://dev.mysql.com/doc/refman/8.0/en/information-functions.html

mysql

文章转载自林员外聊编程，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的函数和运算符 - 信息函数（3）

评论