从mysql获取最后一条记录 [英] Getting last record from mysql

问题描述

我正在使用mysql并面临一些问题。我想要检索插入的最后一行。

I am using mysql and facing some problem. I want to retrieve last row that is inserted.

<<以下是详细信息>>

<< Below are details >>

以下是我创建表的方法。

Below is how I created table.

create table maxID（myID varchar（4））

我在其中插入了四个值，如下所示

I inserted four values in it as below

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

当我执行时选择myID ，last_insert_id（）作为来自maxID 的NewID，我得到如下输出

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 below code,

选择myId，last_insert_id（）作为NewID，@ wingid：= @ rowid + 1作为来自maxID的myrow，（ SELECT @rowid：= 0）as init

我输出如下。

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

但是当我使用代码时选择myId，last_insert_id（）作为NewID，@ warid：= @ rowid + 1 as来自maxID的myrow，（SELECT @rowid：= 0）as init，其中@rowid = 4 ，我收到错误 where where子句中的Uknown列'myrow'

However when I use 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 error as Uknown column 'myrow' in where clause

当我使用其中@ rowid = 4 时，我没有得到表中的任何数据。

When I use where @rowid=4, I don't get any data in tables.

注意：这里我使用4来获得所需的输出。稍后我可以通过查询（从maxID选择max（rowid））

Note: Here I am using 4 just to get desired output. Later I can get this from a query (select max(rowid) from maxID)

请告诉我需要什么要做的话，如果我只想查看最后一条记录，即 A003 。

Please suggest me what need to do if I want to see only last record i.e. A003.

感谢您的时间。

推荐答案

似乎 SELECT 不保证以任何特定顺序返回行（当然，不使用 ORDER BY 子句）。

It seems that a SELECT is not guaranteed to return rows in any specific order (without using an ORDER BY clause, of course).

根据 SQL-92标准（p。 373）：

As per the SQL-92 standard (p. 373):

如果< order by子句>未指定，则由<指定的表指定。游标规范>是T，T中行的顺序依赖于实现。

If an < order by clause > is not specified, then the table specified by the < cursor specification > is T and the ordering of rows in T is implementation-dependent.

好的，MySQL不完全是SQL-92-兼容，但这是一个严重的提示。

Okay, MySQL is not fully SQL-92-compliant, but this is a serious hint.

Laurynas Biveinis（显然隶属于Percona ）还声明：

Laurynas Biveinis (apparently affiliated with Percona) also states:

缺少 ORDER BY 子句（...）的行的顺序可能因月相而不同，即好的。

The order of the rows in the absence of ORDER BY clause (...) could be different due to the moon phase and that is OK.

MySQL手册说InnoDB：

InnoDB总是按照表格行排序至 [a PRIMARY KEY 或 NOT NULL UNIQUE index] 如果存在。

InnoDB always orders table rows according to [a PRIMARY KEY or NOT NULL UNIQUE index] if one is present.

就我而言，我假设 MySQL还可以在 OPTIMIZE TABLE 之后重新排序行，或者甚至在多次删除和插入后重复使用空格（我有试图找到一个这样的例子，到目前为止都失败了。）

As far as I am concerned, I assume MySQL could also reorder rows after an OPTIMIZE TABLE or even reuse empty spaces after many deletes and inserts (I have tried to find an example of this, and have failed so far).

给定不幸的是，你的表格结构，底线是如此多的因素可能改变了行的顺序;我看不到可靠地确定插入顺序的解决方案。除非您在创建表后保留所有二进制日志，当然;）

Given your table structure, the bottomline is, unfortunately, that so many factors could have altered the order of the rows; I see no solution to reliably determine the order they were inserted. Unless you kept all binary logs since you created the table, of course ;)

尽管如此，您可能仍想添加序列列到你的表。现有行将被分配一个可能不准确的序列号，但至少将来的行将被正确排序。

Nevertheless, you may still want to add a sequence column to your table. Existing rows would be assigned a possibly inaccurate sequence number, but at least future rows will be correctly sequenced.

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

这篇关于从mysql获取最后一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！