show table status where Name ='TableName'结果集里列名为AUTO_INCREMENT的值
delete的话,用触发器能得到ID,其他的方法不知道。
create table tb(id int(5) unsigned not null auto_increment, name varchar(60) not null, primary key(id) ) default char set=utf8 engine=InnoDB; insert into tb values(null,"afd"); insert into tb values(null,"afdsa"); insert into tb values(null,"afdsa"); insert into tb values(null,"afdsa"); select * from tb; delete from tb where id<3; insert into tb values(null,"afdsa"); insert into tb values(null,"afdsa"); delete from tb; insert into tb values(null,"afdsa"); 类似这种情况这个最后插入数据的ID怎么获得?请继续帮忙把sql语句写出来
CREATE TABLE animals ( grp ENUM('fish','mammal','bird') NOT NULL, id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (grp,id) ) ENGINE=MyISAM;INSERT INTO animals (grp,name) VALUES ('mammal','dog'),('mammal','cat'), ('bird','penguin'),('fish','lax'),('mammal','whale'), ('bird','ostrich'); INSERT INTO animals (grp,name) VALUES ('mammal','dog'),('mammal','cat'), ('bird','penguin'),('fish','lax'),('mammal','whale'), ('bird','ostrich'); INSERT INTO animals (grp,name) VALUES ('mammal','dog'),('mammal','cat'), ('bird','penguin'),('fish','lax'),('mammal','whale'), ('bird','ostrich');SELECT * FROM animals ;select last_insert_id(); 你说这个是多少?
myisam引擎,这个值是保存在表定义文件里的,所以,每次重新启动都是从表定义文件里面取。清楚这2种本质的情况,相信你就知道你的上面所说的值应该怎么取了。
@@IDENTITY 返回为当前会话的所有作用域中的任何表最后生成的标识值。
SCOPE_IDENTITY 返回为当前会话和当前作用域中的任何表最后生成的标识值。
name varchar(60) not null,
primary key(id)
)
default char set=utf8
engine=InnoDB;
insert into tb values(null,"afd");
insert into tb values(null,"afdsa");
insert into tb values(null,"afdsa");
insert into tb values(null,"afdsa");
select * from tb;
delete from tb where id<3;
insert into tb values(null,"afdsa");
insert into tb values(null,"afdsa");
delete from tb;
insert into tb values(null,"afdsa");
类似这种情况这个最后插入数据的ID怎么获得?请继续帮忙把sql语句写出来
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
) ENGINE=MyISAM;INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');SELECT * FROM animals ;select last_insert_id();
你说这个是多少?
因为对于多行插入,last_insert_id()返回的是第一个插入记录的auto_increment的值。