How to get the next Auto Increment id in MySQL
When we enable Auto Increment on column in a table in MySQL, every time we insert the record in table, The Auto Increment column value increases by 1 automatically.
Let's say we have created table customer with Auto Increment by using below script.
CREATE TABLE `customer` (
`idcustomer` int(11) NOT NULL auto_increment,
`firstname` varchar(50) NULL,
`lastname` varchar(30) NULL,
`age` int(11) DEFAULT NULL,
`phonenumber` char(11) DEFAULT NULL,
`dob` date DEFAULT NULL,
`gender` char(1) NOT NULL,
Constraint pk_idcustomer Primary key (idcustomer)
) ;
Now we are all set to insert the values in table. We don't have to insert anything in idcustomer column as we have set Auto Increment. The first record will have value 1 and then incremented by 1 on each insert.
insert into customer(firstname,lastname,age,phonenumber,dob,gender)
values
('Aamir',
'Shahzad',
39,
'505-4141969',
'1980-01-01',
'M');
Now let's say you have inserted many records in the customer table and you are interested to know "What will be the next Auto Increment Id" for customer table. you can use below script
SELECT Table_schema,Table_Name,Auto_increment
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = "techbrothers"
AND TABLE_NAME = "customer";
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = "techbrothers"
AND TABLE_NAME = "customer";
In above example, My table exits in database "techbrothers" so I have Table_Schema="techbrothers". You need to update to database/ schema in which your table exits. Also you have to provide Table_Name="YourTableName". In my case I have table "customer".
Get the next Auto Increment value id in MySQL or MairaDB
Aivivu chuyên vé máy bay, tham khảo
ReplyDeletevé máy bay đi Mỹ tháng nào rẻ nhất
từ mỹ về việt nam được chưa
vé máy bay từ canada về việt nam giá rẻ
đặt vé máy bay từ nhật về việt nam
vé máy bay từ hàn quốc sang việt nam
Vé máy bay từ Đài Loan về Việt Nam
danh sách khách sạn cách ly đà nẵng
"Avoid revving your engine excessively - it can cause unnecessary wear." mechanic gold coast
ReplyDelete