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!