MySQL has several informative functions that can help us in developing applications or procedures. In this article, I will describe important functions to know which database we are connected to, what our thread (connection) is, how many rows were read, how many rows were affected by a specific update/delete command, and how to capture the last value assigned to an auto_increment column. Let’s take a look at some of them:
– DATABASE(): returns the database to which the connection is linked:
world-> SELECT database();
+—————+
| database() |
+—————+
| world |
+—————+
1 row in set (0.00 sec)
– CONNECTION_ID(): returns your thread number, or if you prefer: the number of your session and/or connection to the database:
world-> select connection_id();
+———————+
| connection_id() |
+———————+
| 2 |
+———————+
1 row in set (0.00 sec)
– LAST_INSERT_ID(): Especially useful when we have tables with AUTO INCREMENT, and after a new insertion, we need to know the last incremented number. To explain better, I will create a table and make some inserts. Remember, it only works with AUTO INCREMENT:
world-> create table telefones
-> (
-> codigo tinyint not null auto_increment,
-> name varchar(25),
-> fone int,
-> primary key(codigo)
-> );
Query OK, 0 rows affected (0.06 sec)
world-> insert into phones values (null, ‘Andre Barros’, 88880102);
Query OK, 1 row affected (0.00 sec)
world-> insert into phones values (null, ‘Olivia Barros’, 88880103);
Query OK, 1 row affected (0.00 sec)
world-> insert into phones values (null, ‘Juju Barros’, 88880104);
Query OK, 1 row affected (0.00 sec)
world-> select last_insert_id();
+——————–+
| last_insert_id() |
+——————–+
| 3 |
+——————–+
1 row in set (0.02 sec)
world-> select * from phones ;
+———+——————–+————–+
| code | name | phones |
+———+——————–+————–+
| 1 | Andre Barros | 88880102 |
| 2 | Olivia Barros | 88880103 |
| 3 | Juju Barros | 88880104 |
+———+——————–+————–+
3 rows in set (0.00 sec)
Note that LAST_INSERT_ID() always returns the last auto-incremented number from the most recent insert in the current session. Therefore, if multiple inserts are sent to the server and you need to know which “code” was assigned to each row, a SELECT LAST_INSERT_ID() must be performed after each insert.
– FOUND_ROWS(): This function returns the number of rows returned by a given SELECT. It is especially useful to avoid, for example, doing a SELECT COUNT(*) just to find out how many rows were returned. Taking advantage of the SELECT * FROM telefones example from the function above:
world-> select found_rows();
+——————+
| found_rows() |
+——————+
| 3 |
+——————+
1 row in set (0.00 sec)
Just like LAST_INSERT_ID(), FOUND_ROWS() returns the number of rows from the last SELECT executed by the current session.
– ROW_COUNT(): Returns the number of rows that were affected, and only those. It works with both UPDATE and DELETE statements.
world-> update phones SET fone = 65110001 where code = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
world-> select row_count();
+—————-+
| row_count() |
+—————-+
| 1 |
+—————-+
1 row in set (0.00 sec)
world-> update phones SET fone = 65110001;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3 Changed: 0 Warnings: 0
world-> select row_count();
+—————-+
| row_count() |
+—————-+
| 0 |
+—————-+
1 row in set (0.00 sec)
world-> delete from phones where code >= 2;
Query OK, 2 rows affected (0.02 sec)
world-> select row_count();
+—————-+
| row_count() |
+—————-+
| 2 |
+—————-+
1 row in set (0.00 sec)
See you later!
Visit our Blog
Learn more about databases
Learn about monitoring with advanced tools
Have questions about our services? Visit our FAQ
Want to see how we’ve helped other companies? Check out what our clients say in these testimonials!
Discover the History of HTI Tecnologia
