If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > Daily Counter with auto_increment

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jul 2009
Posts: 55
Daily Counter with auto_increment

InnoDB:
Is it possible to have the auto increment column of a composite primary key restart from 1 every time the 'date' column value changes?
Code:
CREATE TABLE `odr_orders` (
	`dte` DATE NOT NULL DEFAULT '2013-01-01',
	`odrId` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`odrId`, `dte`)
)
COLLATE = 'utf8_bin'
ENGINE = InnoDB
AUTO_INCREMENT = 1;
Code:
INSERT INTO
		odr_orders
		(
			dte
		)
VALUES
	(
		DATE_FORMAT( NOW(), '%Y-%m-%d' )
	)
as in:
Code:
SET @D:= DATE_FORMAT( NOW(), '%Y-%m-%d' );
SET @I:= (
			SELECT
				MAX( odrId ) + 1
			FROM
				odr_orders
			WHERE
				dte = @D
			);
INSERT INTO
	odr_orders
	(
			dte
		,	odrId
	)
VALUES
	(
			@D
		,	@I
	)
but using auto_increment

Last edited by vivoices; 01-27-13 at 18:00.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Sep 2009
Location: San Sebastian, Spain
Posts: 860
Yes it is possible but you need to change table to type MyISAM and change the order of your primary key. Here is an example:

Code:
mysql> CREATE TABLE `odr_orders` (
    -> `dte` DATE NOT NULL DEFAULT '2013-01-01',
    -> `odrId` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (`dte`, `odrId`)
    -> )
    -> COLLATE = 'utf8_bin'
    -> ENGINE = MyISAM
    -> AUTO_INCREMENT = 1;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into `odr_orders` (`dte`) values ('2013-01-01');
Query OK, 1 row affected (0.00 sec)

mysql> select * from odr_orders;
+------------+-------+
| dte        | odrId |
+------------+-------+
| 2013-01-01 |     1 | 
+------------+-------+
1 row in set (0.00 sec)

mysql> insert into `odr_orders` (`dte`) values ('2013-01-01');
Query OK, 1 row affected (0.00 sec)

mysql> select * from odr_orders;
+------------+-------+
| dte        | odrId |
+------------+-------+
| 2013-01-01 |     1 | 
| 2013-01-01 |     2 | 
+------------+-------+
2 rows in set (0.01 sec)

mysql> insert into `odr_orders` (`dte`) values ('2013-01-02');
Query OK, 1 row affected (0.01 sec)

mysql> select * from odr_orders;
+------------+-------+
| dte        | odrId |
+------------+-------+
| 2013-01-01 |     1 | 
| 2013-01-01 |     2 | 
| 2013-01-02 |     1 | 
+------------+-------+
3 rows in set (0.00 sec)

mysql> insert into `odr_orders` (`dte`) values ('2013-01-01');
Query OK, 1 row affected (0.00 sec)

mysql> select * from odr_orders;
+------------+-------+
| dte        | odrId |
+------------+-------+
| 2013-01-01 |     1 | 
| 2013-01-01 |     2 | 
| 2013-01-01 |     3 | 
| 2013-01-02 |     1 | 
+------------+-------+
4 rows in set (0.00 sec)
__________________
Ronan Cashell
Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
http://www.it-iss.com
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jul 2009
Posts: 55
Thank you Ronan,
your code is only working for "ENGINE = MyISAM".
I need foreign keys so I asked for InnoDB.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Sep 2009
Location: San Sebastian, Spain
Posts: 860
The only way of doing this is using MyISAM, otherwise you will need to write a function to do this yourself.
__________________
Ronan Cashell
Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
http://www.it-iss.com
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jul 2009
Posts: 55
Yes, I thought so.
My SQL from the original post works very well in InnoDB.
I was just hoping to avoid hitting a performance bottleneck for big tables by using auto_increment.
Code:
SET @D:= DATE_FORMAT( NOW(), '%Y-%m-%d' );
SET @I:= (
			SELECT
				MAX( odrId ) + 1
			FROM
				odr_orders
			WHERE
				dte = @D
			);
INSERT INTO
	odr_orders
	(
			dte
		,	odrId
	)
VALUES
	(
			@D
		,	@I
	)
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On