Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL override auto_increment?

Tags:

php

mysql

I'm creating a messaging system (using PHP) and want to assign an ID number to each message (aside from each actual message having an unique ID number)...however, if someone replies to a message then i want to be able to give that message the same ID as the message being replied to...then of course I can disply them by time and show them in order.

So, if i give the field an auto_increment type is that able to be overwritten?

Meaning...each new message has auto value e.g. 1, 2, 3 etc but someone replies to number 2 so it's ID needs to also 2

Or is there a better way to do this?

like image 808
StudioTime Avatar asked Nov 10 '11 12:11

StudioTime


People also ask

Can you override auto increment?

You can insert into an auto-increment column and specify a value. This is fine; it simply overrides the auto-increment generator. If you try to insert a value of NULL or 0 or DEFAULT , or if you omit the auto-increment column from the columns in your INSERT statement, this activates the auto-increment generator.

Can we change auto increment value in MySQL?

In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name. The name of the table whose AUTO_INCREMENT value you wish to change.

Can we have 2 auto increment in MySQL?

You can't have two auto-increment columns.

How do I reset my auto increment primary key?

In MySQL, the syntax to reset the AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = value; table_name. The name of the table whose AUTO_INCREMENT column you wish to reset.


1 Answers

Absolutely nothing prevents you from assigning any arbitrary value to an AUTO_INCREMENT column. If necessary, the table counter will adjust accordingly.

However, you cannot set as AUTO_INCREMENT a column that's not unique.

Honestly, I can't understand your design. A typical messaging system would look like this:

message_id in_reply_to
========== ===========
         1        NULL
         2        NULL
         3           1
         4        NULL
         5           1
         6           3
         7        NULL

Duplicating IDs kind of beats the purpose of using IDs.

Update #1: OMG, it seems that it can actually be done under certain circumstances:

For MyISAM tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html

Update #2: For the records, I've just tested it and you can use duplicate auto-incremented IDs in InnoDB tables as well:

CREATE TABLE foo (
    foo_id INT(10) NOT NULL DEFAULT '0',
    bar_id INT(10) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (foo_id),
    INDEX bar_id (bar_id)
)
ENGINE=InnoDB
like image 91
Álvaro González Avatar answered Sep 30 '22 02:09

Álvaro González