How the CONNECTION_ID() function works in Mariadb?

The CONNECTION_ID() function is an information function that returns the connection ID of the current session. The connection ID is a unique identifier that is assigned to each client that connects to the server. The function returns an unsigned integer value that represents the connection ID.

Syntax

The syntax of the CONNECTION_ID() function is as follows:

CONNECTION_ID()

The function takes no arguments. It can be used in any context where an expression is allowed.

Examples

Example 1: Getting the connection ID of the current session

The following example uses the CONNECTION_ID() function to get the connection ID of the current session.

SELECT CONNECTION_ID();

The output is:

+-----------------+
| CONNECTION_ID() |
+-----------------+
|               3 |
+-----------------+

The output shows that the connection ID of the current session is 1234.

Example 2: Getting the connection ID of another session

The following example uses the CONNECTION_ID() function to get the connection ID of another session. To do this, we need to use the PROCESSLIST table in the information_schema database. The PROCESSLIST table contains information about the currently executing threads on the server.

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
FROM information_schema.PROCESSLIST;

The output is:

+----+------+-----------------+-------+---------+------+----------------------+-------------------------------------------------------------------------------------------+
| ID | USER | HOST            | DB    | COMMAND | TIME | STATE                | INFO                                                                                      |
+----+------+-----------------+-------+---------+------+----------------------+-------------------------------------------------------------------------------------------+
|  5 | root | localhost:50419 | NULL  | Sleep   |   58 |                      | NULL                                                                                      |
|  4 | root | localhost:50418 | test2 | Sleep   |   59 |                      | NULL                                                                                      |
|  3 | root | localhost       | test2 | Query   |    0 | Filling schema table | SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
FROM information_schema.PROCESSLIST |
+----+------+-----------------+-------+---------+------+----------------------+-------------------------------------------------------------------------------------------+

To get the connection ID of the second session, we can use the CONNECTION_ID() function with a subquery that filters the PROCESSLIST table by the ID column.

SELECT CONNECTION_ID() FROM (
  SELECT ID FROM information_schema.PROCESSLIST
  WHERE ID = 3
) AS t;

The output is:

+-----------------+
| CONNECTION_ID() |
+-----------------+
|               3 |
+-----------------+

The output shows that the connection ID of the second session is 2.

Example 3: Comparing the connection ID of the current session with another session

The following example uses the CONNECTION_ID() function to compare the connection ID of the current session with another session. To do this, we can use the = operator to check if the connection IDs are equal.

SELECT CONNECTION_ID() = 2;

The output is:

+---------------------+
| CONNECTION_ID() = 2 |
+---------------------+
|                   0 |
+---------------------+

The output shows that the connection ID of the current session is not equal to 2.

Related Functions

There are some other functions that are related to the CONNECTION_ID() function in Mariadb. They are:

• LAST_INSERT_ID(): This function returns the value of the AUTO_INCREMENT column for the last row that was inserted by the current session.
• USER(): This function returns the user name and host name of the current session.
• CURRENT_USER(): This function returns the user name and host name of the current user, as defined by the server.
• SESSION_USER(): This function returns the user name and host name of the current session, as defined by the client.

Conclusion

The CONNECTION_ID() function is a useful function to get the connection ID of the current session or another session. It can be used to identify the sessions on the server or to compare them. It is similar to the ID column in the PROCESSLIST table, but it can be used in any context where an expression is allowed. It is also related to some other functions that provide information about the current session or user.