• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1386
  • Last Modified:

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

0
jdunneuk
Asked:
jdunneuk
  • 3
  • 2
1 Solution
 
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

0
 
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

0
 
UmeshCommented:
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

0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
UmeshCommented:
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

0
 
UmeshCommented:
Did you try?
0
 
ModernMattCommented:
Code Snippet replaced to remove Sensitive Data

ModernMatt
EE Moderator
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now