I want to get next value that is gonna put in the auto increment column so first of all ,i show what tables i have: userinfo:
"CREATE TABLE userinfo (id int NOT NULL AUTO_INCREMENT,firstname varchar(20) DEFAULT NULL, lastname varchar(20) DEFAULT NULL, login varchar(20) NOT NULL,password varchar(20) NOT NULL, email varchar(30) NOT NULL UNIQUE, typeof varchar(9) DEFAULT 'standart', onoff tinyint(1) NOT NULL, lastvisit date DEFAULT NULL, PRIMARY KEY (id))
I have input two users into userinfo table,here is "select* from userinfo",input is:
id |firstname |lastname |login |password |email |typeof |onoff |lastvisit <br>
1 |Frost |Frost |Frost |givanchy |Frost |standart |1 |NULL <br>
2 |Frost1 |Frost1 |Frost1 |givanchy |Frost1 |standart |1 |NULL
so by logic the next auto increment value should be 3,and i am using next syntax for that:
SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = "kvsl" AND TABLE_NAME = "userinfo",input is:<br>
AUTO_INCREMENT 10
and it is always being 10 WHY?
and by the way the next syntax giving a correct next auto increment value,"show create table kvsl.userinfo",input is:
'userinfo',
'CREATE TABLE `userinfo` (\n `id` int NOT NULL AUTO_INCREMENT,\n `firstname` varchar(20) DEFAULT NULL,\n `lastname` varchar(20) DEFAULT NULL,\n `login` varchar(20) NOT NULL,\n `password` varchar(20) NOT NULL,\n `email` varchar(30) NOT NULL,\n `typeof` varchar(9) DEFAULT \'standart\',\n `onoff` tinyint(1) NOT NULL,\n `lastvisit` date DEFAULT NULL,\n PRIMARY KEY (`id`),\n UNIQUE KEY `login` (`login`),\n UNIQUE KEY `email` (`email`)\n) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'
how you can see the next to insert auto_increment value is 3
why is that?
because i need to return in int variable the next auto_increment value;
what is the best way to get next or current auto increment value to store in variable like this
begin
declare curvala int;
set int =(SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = "kvsl" AND TABLE_NAME = "userinfo");
end;//
