Geek asks and answers

Getting last record from mysql

I am using mysql and am encountering some problems. I want to get the last row inserted.

<<Below are the details →

The following is a way to create a table.

create table maxID (myID varchar(4))

I inserted four values ​​into it as shown below

insert into maxID values ('A001')
insert into maxID values ('A002')
insert into maxID values ('A004')
insert into maxID values ('A003')

When I execute select myID, last_insert_id() as NewID from maxID, I get output as below

myId NewID
A001   0  
A002   0  
A004   0  
A003   0

When I tried under the code,

select myId, last_insert_id() as NewID, @rowid:=@rowid+1 as myrow from maxID, (SELECT @rowid:=0) as init

I get the output as shown below.

myId NewID  rowid
A001   0      1
A002   0      2
A004   0      3
A003   0      4

However, when I use the code select myId, last_insert_id() as NewID, @rowid:=@rowid+1 as myrow from maxID, (SELECT @rowid:=0) as init where @rowid = 4, I get an error likeUknown column 'myrow' in where clause

When I use where @rowid=4, I do not get any data in the tables.

Playback link with data

Note: Here I use 4 to get the desired result. Later I can get this from the request(select max(rowid) from maxID)

, , , , .. A003.

.

Update:

, , .

+2
6

, SELECT ( ORDER BY, ).

SQL-92 (. 373):

< order by → , , < a > T, T .

, MySQL SQL-92, .

Laurynas Biveinis (- Percona) :

ORDER BY (...) - , .

MySQL InnoDB:

InnoDB [a PRIMARY KEY NOT NULL UNIQUE index], .

, , MySQL OPTIMIZE TABLE ( ).

, , , ; , . , ;)

, sequence . , , , .

ALTER TABLE maxID ADD sequence INT DEFAULT NULL;
ALTER TABLE maxID ADD INDEX(sequence);
ALTER TABLE maxID MODIFY sequence INT AUTO_INCREMENT;

http://sqlfiddle.com/#!2/63a8d/1

+2

. . :

select myId, @rowid:=@rowid+1 as myrow from maxID, (SELECT @rowid:=0) as init ORDER BY myrow desc LIMIT 1;

:

mysql> select myId, @rowid:=@rowid+1 as myrow from maxID, (SELECT @rowid:=0) as
init ORDER BY myrow desc LIMIT 1;
+------+-------+
| myId | myrow |
+------+-------+
| A003 |     4 |
+------+-------+
1 row in set (0.00 sec)

UPDATE

. . , . , SELECT (, ,). :

  • SELECT ?
  • ?

, , .

+5

script, A004 . . ( A004),

select myID from maxID order by myID desc limit 1

, ? . PK ( , , ).

+2

MySQL help last_insert_id, ** . , MySQL , - . ,

SELECT *
FROM maxID
WHERE myId = max(myId)

, = last_insert_id() WHERE. . , .

+1

last_insert_id , , . , . , . , last_insert_id(). ( ), .

varchar, , , , .

Another solution that I can come up with can do what you need - create a stored procedure that creates a row, inserts it into the table and then returns it to you.

Your use of @rowid is simply counting the order lines that are returned to you. There is no guarantee that they will be returned to you in order from oldest to newest. There are many things that can affect the storage order of strings.

0
source

Please use the following query.

Select * from maxID order by myId desc limit 1,1;
-1
source

More articles:


All Articles