KQpDte0K

1Making another mysql auto increment colomn?
2CREATE TABLE IF NOT EXISTS `parts` (
3  `id` int(11) NOT NULL AUTO_INCREMENT,
4  `name` varchar(255) NOT NULL,
5  `order` int(11) NOT NULL,
6  PRIMARY KEY (`id`)
7) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
8	
9CREATE TABLE IF NOT EXISTS `parts` (
10  `id` int(11) NOT NULL,
11  `name` varchar(255) NOT NULL,
12  `order` int(11) NOT NULL AUTO_INCREMENT,
13  PRIMARY KEY (`id`, `order`)
14) ENGINE=myisam DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
15
16-- Add some new rows with manually auto-incremented id:
17
18--    SELECT COALESCE(MAX(id), 0) + 1 INTO @next_id FROM parts;
19--    INSERT INTO parts VALUES(@next_id, '', NULL);
20--    SELECT COALESCE(MAX(id), 0) + 1 INTO @next_id FROM parts;
21--    INSERT INTO parts VALUES(@next_id, '', NULL);
22
23  INSERT INTO parts SELECT COALESCE(MAX(id), 0) + 1, '', NULL FROM parts;
24  INSERT INTO parts SELECT COALESCE(MAX(id), 0) + 1, '', NULL FROM parts;
25
26SELECT * FROM parts;
27+----+------+-------+
28| id | name | order |
29+----+------+-------+
30|  1 |      |     1 |
31|  2 |      |     1 |
32+----+------+-------+
33
34
35-- Add some new rows for specified `id`, the value for `order` field will be set automatically:
36
37INSERT INTO parts VALUES(2, '', NULL);
38INSERT INTO parts VALUES(2, '', NULL);
39
40+----+------+-------+
41| id | name | order |
42+----+------+-------+
43|  1 |      |     1 |
44|  2 |      |     1 |
45|  2 |      |     2 |
46|  2 |      |     3 |
47+----+------+-------+
48	
49CREATE TABLE IF NOT EXISTS `parts_order` (
50  `order` int(11) NOT NULL AUTO_INCREMENT,
51  PRIMARY KEY (`order`)
52) ENGINE=MyISAM;
53	
54-- First we insert a new empty row into `parts_order`
55INSERT INTO `parts_order` VALUES (NULL);
56-- Next we insert a new row into `parts` with last inserted id from `parts_order`
57INSERT INTO `parts` SET `name` = "new name", `order` = LAST_INSERT_ID();
58	
59CREATE TABLE IF NOT EXISTS `parts` (
60  `id` int(11) NOT NULL AUTO_INCREMENT,
61  `name` varchar(255) NOT NULL,
62  `order` int(11) NOT NULL,
63  PRIMARY KEY (`id`)
64) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
65	
66INSERT INTO `parts` (`name`, `order`) 
67SELECT 'name of new part', IFNULL(MAX(`order`), 0) + 1 FROM parts;
68	
69LOCK TABLES `parts` AS t1 WRITE, `parts` WRITE;
70
71INSERT INTO `parts` (`name`, `order`) 
72SELECT 'name of new part', IFNULL(MAX(`order`), 0) + 1 FROM `parts` AS t1;
73
74UNLOCK TABLES;