MySQL INFORMATION FUNCTIONS

Last update on August 10 2023 12:52:22 (UTC/GMT +8 hours)

INFORMATION FUNCTIONS

Contents:

MySQL BENCHMARK() Function
MySQL CHARSET() Function
MySQL COERCIBILITY() Function
MySQL COLLATION() Function
MySQL CONNECTION_ID() Function
MySQL CURRENT_USER(), CURRENT_USER Function
MySQL DATABASE() Function
MySQL FOUND_ROWS() Function
MySQL LAST_INSERT_ID() Function
MySQL ROW_COUNT() Function
MySQL SCHEMA() Function
MySQL USER() Function
MySQL SESSION_USER() Function
MySQL SYSTEM_USER() Function
MySQL VERSION() Function

BENCHMARK() Function

In MySQL the BENCHMARK() is used to know the time of execution of a query, i.e. how quickly MySQL processes an expression. It returns always 0.

The time returns is having passed time on the client end, not CPU time on the server end.

BENCHMARK() is knowing for measuring the performance of runtime of only scalar expressions, although the expression can be a subquery, and it must return a single column and at most a single row.

Syntax:

BENCHMARK(count,expr)

Example:

SELECT BENCHMARK(100,ENCODE('Hi','welcome'));
+---------------------------------------+
| BENCHMARK(100,ENCODE('Hi','welcome')) |
+---------------------------------------+
|                                     0 | 
+---------------------------------------+
1 row in set (0.00 sec)

SELECT BENCHMARK(100,(SELECT * FROM test2)));
+--------------------------------------+
| BENCHMARK(100,(SELECT * FROM test2)) |
+--------------------------------------+
|                                    0 | 
+--------------------------------------+
1 row in set (0.00 sec)

Here in the above, in the last example, assumed that the test2 file have only one column and a row.

MySQL CHARSET() Function

In MySQL, the CHARSET() function is used to returns the character set of the string argument.

Syntax:

CHARSET(str)

str is the string argument.

Example:

mysql> SELECT CHARSET('xyz');
+----------------+
| CHARSET('xyz') |
+----------------+
| latin1         | 
+----------------+
1 row in set (0.00 sec)


mysql> SELECT CHARSET(CONVERT('xyz' USING cp1251));
+--------------------------------------+
| CHARSET(CONVERT('xyz' USING cp1251)) |
+--------------------------------------+
| cp1251                               | 
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CHARSET(USER());
+-----------------+
| CHARSET(USER()) |
+-----------------+
| utf8            | 
+-----------------+
1 row in set (0.00 sec)

MySQL COERCIBILITY() Function

In MySQL, the COERCIBILITY() function is used to return the collation coercibility value of the string argument.

Syntax:

COERCIBILITY(str)

str is the string argument.

Example:

mysql> SELECT COERCIBILITY('xyz' COLLATE latin1_swedish_ci);
+-----------------------------------------------+
| COERCIBILITY('xyz' COLLATE latin1_swedish_ci) |
+-----------------------------------------------+
|                                             0 | 
+-----------------------------------------------+
1 row in set (0.00 sec)


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

mysql> SELECT COERCIBILITY(concat('aaa','ddd'));
+-----------------------------------+
| COERCIBILITY(concat('aaa','ddd')) |
+-----------------------------------+
|                                 4 | 
+-----------------------------------+
1 row in set (0.01 sec)

From the documentation:

The return values have the meanings shown in the following table. Lower values have higher precedence.

Coercibility	Meaning	Example
0	Explicit collation	Value with COLLATE clause
1	No collation	Concatenation of strings with different collations
2	Implicit collation	Column value
3	System constant	USER() return value
4	Coercible	Literal string
5	Ignorable	NULL or an expression derived from NULL

MySQL COLLATION() Function

In MySQL, the COLLATION() function is used to return the collation of the string argument.

Syntax:

COLLATION(str)

str is the string argument.

Example:

mysql> SELECT COLLATION('xyz');
+-------------------+
| COLLATION('xyz')  |
+-------------------+
| latin1_swedish_ci | 
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT COLLATION(_cp1251'xyz');
+-------------------------+
| COLLATION(_cp1251'xyz') |
+-------------------------+
| cp1251_general_ci       | 
+-------------------------+
1 row in set (0.00 sec)

MySQL CONNECTION_ID() Function

In MySQL the CONNECTION_ID() function is used to return the connection ID for the connection. Every connection has an ID that is unique among the set of currently connected clients.

Syntax:

CONNECTION_ID()

Example:

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|               4 | 
+-----------------+
1 row in set (0.02 sec))

MySQL CURRENT_USER(),CURRENT_USER

In MySQL the CURRENT_USER() function is used to return the user name and host name combination for the MySQL account that the server used to authenticate the current client. This account determines your access privileges. The return value is a string in the utf8 character set.

The value of CURRENT_USER() and USER() can differ.

Example:

mysql> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost | 
+----------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost | 
+----------------+
1 row in set (0.00 sec)

MySQL DATABASE() Function

In MySQL the DATABASE() function is used to return the current (default ) database name as a string in the utf8 character set. It returns NULL if there is no default database.

Example:

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| empoloyee  | 
+------------+
1 row in set (0.00 sec)

MySQL FOUND_ROWS() Function

In MySQL, a LIMIT clause along with a SELECT statement is used to restrict the number of rows the server returns to the client. However, it is to be needed that, how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, a SQL_CALC_FOUND_ROWS option can be included in the SELECT statement, and then use FOUND_ROWS() to know how many rows the last query have returned.

Example:

mysql> SELECT SQL_CALC_FOUND_ROWS * 
FROM employees WHERE employee_id > 100 LIMIT 50,10;

mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|          106 | 
+--------------+
1 row in set (0.00 sec)

Here in the above example, the first query will return (display) 10 rows in the table employees, for which the condition employee_id > 100 . The second call to the SELECT command returns the number of rows that would be returned by the first command SELECT if it had been written without expression LIMIT. Although using the command SELECT SQL_CALC_FOUND_ROWS, MySQL must count all rows in the result set, this method is still faster than without LIMIT because they do not want to send the result to the client.

The purpose of SQL_CALC_FOUND_ROWS for UNION is that it should return the number of rows that will be returned without a global LIMIT.

SQL_CALC_FOUND_ROWS keyword must be specified in the first SELECT statement.
Meaning FOUND_ROWS () will be accurate only if a UNION ALL. If specified without UNION ALL, deduplication occurs, and the value FOUND_ROWS () may be approximate.
If not present in the UNION LIMIT, SQL_CALC_FOUND_ROWS is ignored and returns the number of rows in the temporary table that is created to perform the UNION.

MySQL LAST_INSERT_ID()

In MySQL the LAST_INSERT_ID() function is used to return the identification number of the last row inserted using the MySQL connection.

This function returns the identification number of the last row inserted using the MySQL connection. Identification numbers that were set manually when rows are inserted, without the use of AUTO_INCREMENT, won't register and therefore won't be returned by LAST_INSERT_ID( ). If multiple rows are inserted by one SQL statement, LAST_INSERT_ID( ) returns the identification number for the first row inserted.

Syntax:

LAST_INSERT_ID() 
or
LAST_INSERT_ID(expr)

Example:

CREATE TABLE test (
       id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
       col1 VARCHAR(10) NOT NULL
     );
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO test VALUES (NULL, '1st row');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+----+---------+
| id | col1    |
+----+---------+
|  1 | 1st row | 
+----+---------+
1 row in set (0.00 sec)

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

mysql> INSERT INTO test VALUES 
    -> (NULL, '2nd row'),(NULL, '3rd row'),(NULL, '4th row');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test;
+----+---------+
| id | col1    |
+----+---------+
|  1 | 1st row | 
|  2 | 2nd row | 
|  3 | 3rd row | 
|  4 | 4th row | 
+----+---------+
4 rows in set (0.00 sec)

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

Although the second INSERT statement inserted three new rows into test table, the ID generated for the first of these rows was 2, and the value returned by LAST_INSERT_ID() for the following SELECT statement is 2. So, we can say that, if we insert many rows by one select statement, the LAST_INSERT_ID() will show the result, from which ID the last insertion had been started.

MySQL ROW_COUNT() Function

In MySQL the ROW_COUNT() function is used to return the number of rows affected by the previous SQL statement. If the previous statement was not one that could potentially change data rows or you can say, it wasn't an INSERT, UPDATE, DELETE or other such statement this function will return -1.

Example:

mysql> CREATE TABLE test (
    ->        id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->        col1 VARCHAR(10) NOT NULL
    ->      );
Query OK, 0 rows affected (0.03 sec)

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

mysql> INSERT INTO test VALUES (NULL, '1st row');
Query OK, 1 row affected (0.00 sec)

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

mysql> INSERT INTO test VALUES 
    -> (NULL, '2nd row'),(NULL, '3rd row'),(NULL, '4th row');
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> SELECT * FROM test;
+----+---------+
| id | col1    |
+----+---------+
|  1 | 1st row | 
|  2 | 2nd row | 
|  3 | 3rd row | 
|  4 | 4th row | 
+----+---------+
4 rows in set (0.00 sec)

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

mysql> UPDATE test SET col1='2nd row of test' WHERE ID=2;
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 1

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

mysql> DELETE FROM test WHERE ID=4;
Query OK, 1 row affected (0.00 sec)

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

mysql> ALTER TABLE test ADD col2 VARCHAR(10) NOT NULL;
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

mysql> DROP TABLE test;
Query OK, 0 rows affected (0.00 sec)

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

Here in the above example after CREATE TABLE, ALTER TABLE, DROP TABLE, SELECT QUERY the ROW_COUNT statement returns -1, other than returns the number of affected rows.

MySQL SCHEMA() Function

In MySQL the SCHEMA() function is used to return the current (default ) schema name as a string in the utf8 character set. It returns NULL if there is no default database. This function is a synonym for DATABASE().

Example:

mysql> SELECT SCHEMA();
+-----------+
| SCHEMA()  |
+-----------+
| empoloyee | 
+-----------+
1 row in set (0.00 sec)

MySQL USER() Function

In MySQL the USER() function returns the current MySQL user name and host name as a string in the utf8 character set.

Example:

mysql> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost | 
+----------------+
1 row in set (0.00 sec)

In the above example the value indicates the user name specified when connecting to the server, and the client host from which the client have been connected. The value user can be different from that of CURRENT_USER().

MySQL SESSION_USER() Function

This function is same as the MySQL USER(). See above.

MySQL SYSTEM_USER() Function

This function is same as the MySQL USER(). See above.

MySQL VERSION() Function

In MySQL the VERSION() function is used to return a string that indicates the MySQL server version. The string uses the utf8 character set. There might have a suffix in after the version number.

Example:

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

Previous: MySQL Cast functions and Operators
Next: MySQL XML Functions