CODEWITHSUNDEEP
mysql
Chris Muench
Chris Muench

Reputation: 18348

mysqldump and autoincrement and inserting value

I was looking at the mysqldump file that was generated and noticed something odd. The AUTO_INCREMENT when the table is created is 2. The first inserts uses 1. Is there anything wrong about inserting a value into the database that is below the auto increment value?

DROP TABLE IF EXISTS `phppos_locations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `phppos_locations` (
  `location_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) COLLATE utf8_unicode_ci DEFAULT '',
  `address` tinytext COLLATE utf8_unicode_ci,
  `phone` varchar(30) COLLATE utf8_unicode_ci DEFAULT '',
  `fax` varchar(30) COLLATE utf8_unicode_ci DEFAULT '',
  `email` varchar(100) COLLATE utf8_unicode_ci DEFAULT '',
  `receive_stock_alert` int(1) DEFAULT '0',
  `stock_alert_email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `return_policy` text COLLATE utf8_unicode_ci,
  `timezone` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mailchimp_api_key` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `enable_credit_card_processing` int(1) DEFAULT NULL,
  `merchant_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `merchant_password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `default_tax_1_rate` decimal(15,3) DEFAULT NULL,
  `default_tax_1_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `default_tax_2_rate` decimal(15,3) DEFAULT NULL,
  `default_tax_2_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `default_tax_2_cumulative` int(1) DEFAULT '0',
  `deleted` int(1) DEFAULT '0',
  PRIMARY KEY (`location_id`),
  KEY `deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `phppos_locations`
--

LOCK TABLES `phppos_locations` WRITE;
/*!40000 ALTER TABLE `phppos_locations` DISABLE KEYS */;
INSERT INTO `phppos_locations` VALUES (1,'Default','123 Nowhere street','555-555-5555','','[email protected]',NULL,NULL,'Test','America/New_York',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0);
/*!40000 ALTER TABLE `phppos_locations` ENABLE KEYS */;
UNLOCK TABLES;

Upvotes: 1

Views: 1736

Answers (2)

Ike Walker
Ike Walker

Reputation: 65587

The AUTO_INCREMENT value in the CREATE TABLE statement represents the auto-increment value that MySQL intended to use for the next insert on that table, as of the time you dumped the schema. Since there was one row in the table at that time, and it had an auto-increment value of 1, it makes sense that 2 was the next intended value.

Since the insert explicitly sets the location_id to be 1 it does not use auto-increment for that row.

Upvotes: 0

PasteBT
PasteBT

Reputation: 2198

copy from http://dev.mysql.com/doc/refman/5.1/en/create-table.html

An integer or floating-point column can have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. Typically this is value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1.

Which means if you insert not NULL or 0, will keep what you insert

Upvotes: 1

Related Questions