How to query the last record in mysql

Mysql method to query the last record: 1. View the table and table structure in the current database; 2. Sort the ID field in reverse order according to the ID auto-increment and view the first row; 3. According to the current insert statement and use the function last_insert_id() to view the last record.

The operating environment of this article: Windows 7 system, mysql version 8.0, Dell G3 computer.

How does mysql query the last record?

MySQL queries the last record

1. Environment and data preparation

1. View the table in the current database

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| my_insert1 |
| my_insert2 |
+----------------+

2. View the my_insert1 table structure

mysql> show create table my_insert1\G;
*************************** 1. row ***************************
Table: my_insert1
Create Table: CREATE TABLE `my_insert1` (
`name` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
`password` varchar(32) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3. View the my_insert2 table structure

mysql> show create table my_insert2\G;
*************************** 1. row ***************************
       Table: my_insert2
Create Table: CREATE TABLE `my_insert2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
  `password` varchar(32) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

4. To the table my_insert1 and Insert records into the my_insert2 table

mysql> insert into my_insert1(name,password) values ('黄飞鸿',password(123456)),('李小龙',password(123456));
 
mysql> insert into my_insert2(id,name,password) values (null,'黄飞鸿',password(123456)),(null,'李小龙',password(123456));

5. View the records of the table

mysql> select * from my_insert1;
+-----------+----------------------------------+
| name      | password                         |
+-----------+----------------------------------+
| 黄飞鸿    | *6BB4837EB74329105EE4568DDA7DC67 |
| 李小龙    | *6BB4837EB74329105EE4568DDA7DC67 |
+-----------+----------------------------------+
 
mysql> select * from my_insert2;
+----+-----------+----------------------------------+
| id | name      | password                         |
+----+-----------+----------------------------------+
|  1 | 黄飞鸿    | *6BB4837EB74329105EE4568DDA7DC67 |
|  2 | 李小龙    | *6BB4837EB74329105EE4568DDA7DC67 |
|  3 | 李连杰    | *6BB4837EB74329105EE4568DDA7DC67 |
+----+-----------+----------------------------------+

2. How to view the last record when there is no ID auto-increment field in the table and there is an ID auto-increment field

1. Due to my_insert1, there is no ID auto-increment, check how many records there are in the current table

mysql> select count(*) from my_insert1;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)

2. Check the second row record of the current table

mysql> select * from my_insert1 limit 1,1;
+-----------+----------------------------------+
| name | password |
+-----------+----------------------------------+
| 李小龙 | *6BB4837EB74329105EE4568DDA7DC67 |
+-----------+----------------------------------+
1 row in set (0.00 sec)

3. Auto-increment based on the ID , use subquery to view the maximum value of the table ID field

mysql> select *   from my_insert2 where id=(select max(id) from my_insert2);
+----+-----------+----------------------------------+
| id | name      | password                         |
+----+-----------+----------------------------------+
|  3 | 李连杰    | *6BB4837EB74329105EE4568DDA7DC67 |
+----+-----------+----------------------------------+

4. Sort the ID field in reverse order according to the ID auto-increment, and view the first row

mysql> select * from my_insert2 order by id desc limit 1;
+----+-----------+----------------------------------+
| id | name      | password                         |
+----+-----------+----------------------------------+
|  3 | 李连杰    | *6BB4837EB74329105EE4568DDA7DC67 |
+----+-----------+----------------------------------+

5. You can according to the current insert statement Use the function last_insert_id() to view the last record

mysql> insert into my_insert2(id,name,password) values(null,'霍元甲',password('123456'));
Query OK, 1 row affected, 1 warning (0.00 sec)
 
mysql> select * from my_insert2 where id=(select last_insert_id());
+----+-----------+----------------------------------+
| id | name      | password                         |
+----+-----------+----------------------------------+
|  4 | 霍元甲    | *6BB4837EB74329105EE4568DDA7DC67 |
+----+-----------+----------------------------------+
1 row in set (0.00 sec)

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to query the last record in mysql. For more information, please follow other related articles on the PHP Chinese website!