如何忽略myIsam插入时自动增加的主ID忽略？

Question

How to ignore automatically increased primary id on myIsam insert ignore? How to solve this problem? This is increased primary id rapidly. How to solve this

MY TABLE STRUCTURE

    | dates_tbl | CREATE TABLE `dates_tbl` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `date_raw` int(8) unsigned NOT NULL DEFAULT '0',
  `date` date NOT NULL DEFAULT '0000-00-00',
  `day` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `week` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `month` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `year` year(4) NOT NULL DEFAULT '0000',
  `created_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `date_raw_unique` (`date_raw`),
  KEY `date_raw` (`date_raw`),
  KEY `month_year` (`month`,`year`),
  KEY `year` (`year`)
) ENGINE=InnoDB AUTO_INCREMENT=21628 DEFAULT CHARSET=latin1 |

INSERT QUERY

    $q = "INSERT IGNORE INTO dates_tbl(date_raw,date,day,week,month,year,created_on) 
values $dynamic_value";

RESULT

mysql> select id from dates_tbl limit 10;
+-------+
| id    |
+-------+
| 19657 |
| 19681 |
| 19729 |
| 19777 |
| 19825 |
| 19873 |
| 19884 |
| 19913 |
| 19960 |
| 20007 |
+-------+
10 rows in set (0.01 sec)

Answer 1

• The table is InnoDB, why do you mention MyISAM?
• Get rid of id, it probably serves no purpose. Instead, promote date_raw to be the PRIMARY KEY.
• Avoid redundant indexes -- a PRIMARY KEY is a UNIQUE KEY is a KEY.
• Usually it is better to keep year+month+day in a single DATE column, then pick it apart when needed.
• Do you actually use created_on and modified_on? Or is that an artifact of some 3rd party software?
• It is more efficient to have an index on a DATE, then use something like

this:

WHERE ymd >= '2010-03-01'
  AND ymd  < '2010-03-01' + INTERVAL 1 MONTH

With those changes, you have eliminated half the columns and most of the indexes. And the INSERT IGNORE will stop giving you troubles.

If you keep the AUTO_INCREMENT, then let's see some more of the logic -- need to see why IGNORE is taking effect. The solution may involve some of the other code in addition to the INSERT.

Of this is part of a star schema in Data Warehousing, then I will rant about how it is bad to normalize "continuous" values, such as DATE. At that point, the whole table vanishes. And the code will run faster!