MYSQL Auto Increment

Hi guys,


have a simple problem I hope,

I want to use auto increment on the product_type table but need to keep it as 10001 10002 however this is what i shappening

record id

100001
100002
3


thats what is happening :(

thanks

John
/*
SQLyog Community Edition- MySQL GUI v7.02 
MySQL - 5.0.27-community-nt : Database - xyz
*********************************************************************
*/
 
 
/*!40101 SET NAMES utf8 */;
 
/*!40101 SET SQL_MODE=''*/;
 
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
 
CREATE DATABASE /*!32312 IF NOT EXISTS*/`xyz` /*!40100 DEFAULT CHARACTER SET latin1 */;
 
USE `xyz`;
 
/*Table structure for table `manufacturer` */
 
DROP TABLE IF EXISTS `manufacturer`;
 
CREATE TABLE `manufacturer` (
  `id` int(3) unsigned zerofill NOT NULL auto_increment,
  `name` varchar(45) default NULL,
  `addressln1` varchar(255) default NULL,
  `addressln2` varchar(255) default NULL,
  `county` varchar(45) default NULL,
  `country` varchar(255) default NULL,
  `telephone` varchar(45) default NULL,
  `pic` varchar(255) default 'nofind.gif',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
/*Table structure for table `product_type` */
 
DROP TABLE IF EXISTS `product_type`;
 
CREATE TABLE `product_type` (
  `id` int(5) unsigned zerofill NOT NULL auto_increment,
  `name` varchar(45) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
/*Table structure for table `products` */
 
DROP TABLE IF EXISTS `products`;
 
CREATE TABLE `products` (
  `id` int(5) unsigned zerofill NOT NULL auto_increment,
  `name` int(5) unsigned zerofill default NULL,
  `description` varchar(250) default NULL,
  `model` varchar(45) default NULL,
  `weight` double default NULL,
  `partnum` varchar(45) default NULL,
  `cc` double default NULL,
  `hp` double default NULL,
  `wheeldiam` double default NULL,
  `maxcutdept` double default NULL,
  `kw` double default NULL,
  `bladesize` double default NULL,
  `pic` varchar(255) default 'nofind.gif',
  `manufacturer` int(3) unsigned zerofill NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `FK_product` (`name`),
  KEY `FK_products` (`manufacturer`),
  CONSTRAINT `FK_products` FOREIGN KEY (`manufacturer`) REFERENCES `manufacturer` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `FK_product` FOREIGN KEY (`name`) REFERENCES `product_type` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
/*Table structure for table `users` */
 
DROP TABLE IF EXISTS `users`;
 
CREATE TABLE `users` (
  `id` int(2) NOT NULL,
  `name` varchar(45) default NULL,
  `username` varchar(45) default NULL,
  `password` varchar(45) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
 
Open in New Window Select All Tags: 
MYSQL

Open in new window

LVL 3
jdunneukAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aleksandar BradarićSoftware DeveloperCommented:
You have to modify the table so it knows where to start the auto increment counter. Try this and the next value will be 100003:
ALTER TABLE yourTable AUTO_INCREMENT = 100003;

Open in new window

Aleksandar BradarićSoftware DeveloperCommented:
Here's the test case:
mysql> INSERT INTO yourTable VALUES(null);
Query OK, 1 row affected (0.03 sec)
 
mysql> SELECT * FROM yourTable;
+----+
| id |
+----+
|  1 | 
+----+
1 row in set (0.00 sec)
 
mysql> ALTER TABLE yourTable AUTO_INCREMENT = 100001;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO yourTable VALUES(null);
Query OK, 1 row affected (0.00 sec)
 
mysql> SELECT * FROM yourTable;
+--------+
| id     |
+--------+
|      1 | 
| 100001 | 
+--------+
2 rows in set (0.00 sec)

Open in new window

UmeshSenior Principal Technical Support EngineerCommented:
Also his should do the trick...
CREATE TABLE `product_type` (
  `id` int(5) unsigned zerofill NOT NULL auto_increment,
  `name` varchar(45) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT = 100001;
 
 
insert into product_type values(null,"hello"),(null,"hello"),(null,"hello"),(null,"hi");
 
select * from product_type;
 
100001	hello
100002	hi
100003	hello
100004	hi
\N	\N

Open in new window

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

UmeshSenior Principal Technical Support EngineerCommented:
BTW, zerofill is also not required in the table definition.. you can ommit..
CREATE TABLE `product_type` (
  `id` int unsigned NOT NULL auto_increment,
  `name` varchar(45) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT = 100001;
 
 
insert into product_type values(null,"hello"),(null,"hello"),(null,"hello"),(null,"hi");
 
 
select * from product_type;
 id     name
100001  hello
100002  hi
100003  hello
100004  hi

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
UmeshSenior Principal Technical Support EngineerCommented:
Did you try?
ModernMattCommented:
Code Snippet replaced to remove Sensitive Data

ModernMatt
EE Moderator
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.