Extract the user ID from the username only in MySQL?

To extract the User ID only from MySQL, you can use SUBSTRING_INDEX(), which extracts the part of a string from the Username to get the User ID. 

Let us first display the user −

mysql> SELECT USER();

This will produce the following output −

+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

Let us now extract the UserID only −

mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);

This will produce the following output −

+-------------------------------+
| SUBSTRING_INDEX(USER(),'@',1) |
+-------------------------------+
| root                          |
+-------------------------------+
1 row in set (0.00 sec)

Additionally, if you want the host name, you can use the below query −

mysql> SELECT SUBSTRING_INDEX(USER(),'@',-1);

This will produce the following output −

+--------------------------------+
| SUBSTRING_INDEX(USER(),'@',-1) |
+--------------------------------+
| localhost                      |
+--------------------------------+
1 row in set (0.00 sec)