How to reset seq if the cdate changed?

Databases
1

Dear All,

I have a table with an auto-increment field seq,
the primary key are: seq,cdate,ccid

how to reset seq if the cdate changed?

please help

thanks & regards


mysql> use matrixcti;
Database changed
mysql> describe serviceinit;
+---------------+------------+------+-----+------------+----------------+
| Field         | Type       | Null | Key | Default    | Extra          |
+---------------+------------+------+-----+------------+----------------+
| ccid          | bigint(20) | NO   | PRI |            |                |
| cdate         | date       | NO   | PRI | 0000-00-00 |                |
| ctime         | time       | YES  |     | NULL       |                |
| seq           | bigint(20) | NO   | PRI | NULL       | auto_increment |
| ccallid       | bigint(20) | YES  |     | NULL       |                |
| staticid      | bigint(20) | YES  |     | NULL       |                |
| cause         | bigint(20) | YES  |     | NULL       |                |
| localcallinfo | bigint(20) | YES  |     | NULL       |                |
| period        | char(6)    | YES  |     | NULL       |                |
| isdone        | tinyint(1) | YES  |     | NULL       |                |
+---------------+------------+------+-----+------------+----------------+
10 rows in set (0.01 sec)

mysql>
2

the way you are using the auto_increment (as part of a multi-column key) it will only work in myisam

however, if you are adding “extreme incoming and outgoing records per day” you shoud probably consider innodb to avoid the table lock that myisam uses

3

how many per day?

4

I just want to prevent that it would not reach the max of its data-type…
fyi, my application will dealing with a huge PBX system that will generate extreme incoming and outgoing records per day.
it would not reach in daily basis … but I think it would reach the max. value of its data-type in at least 1, 2 or 3 years
any idea?

5

noted with thanks, I would use InnoDB and try another way for doing this…

6

I don’t know, as far as I knew is this is an engine issue…
could you please advise what the best engine should I use InnoDB or MyISAM ?

7

regarding resetting the auto_increment: don’t

as for the difference between INTEGER and BIGINT, let me ask you a question – how many new rows to you expect to add to the table every day?

8

my purpose is to be uniquely but I want the auto-increment column to be reset everyday… is it possible?

could you please advise, why I should use int rather than bigint? … since it’s an auto_increment column so I am worry that it would reach the max value of its data type.

as far as I know is int(11) and bigint(20)

thanks

9

here’s how to do it: don’t

the purpose of an auto_increment column is to guarantee uniqueness, and that’s all

any attempt to use the sequencing of the numbers will be an epic fail

for sequencing, use a datetime column

by the way, your date and time columns should be combined, and you should use INTEGER, not BIGINT

:slight_smile: