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)