Results 1 to 5 of 5
  1. #1
    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 19:00.

  2. #2
    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

  3. #3
    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.

  4. #4
    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

  5. #5
    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
    	)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •