MySQL overrides auto_increment?

Question

I am creating a messaging system (using PHP) and I want to assign an identification number for each message (except for each actual message with a unique identification number) ... however, if someone replies to a message, then I want to be able to send that message same identifier as the message to which the message was sent ... then, of course, I can tell them in time and show them in order.

So, if I give a field, can the auto_increment type be overwritten?

Value ... each new message has an automatic value, for example. 1, 2, 3, etc., but someone answers number 2, so he also needs 2

Or is there a better way to do this?

Answer 1

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

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

Honestly, I do not 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 

Duplication of identifiers leads to the use of identifiers.

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

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

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

Update # 2:. For the records, I just tested it, and you can also use duplicate auto-increment identifiers in InnoDB tables:

 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 

Answer 2

No, auto_increment columns cannot occur multiple times.

Answer 3

I would save every unique message identifier - whether using auto-increment or uuid. Add an additional column to the message structure for thread_id - unique when created, and then all responses include this thread_id to bind them logically.

Answer 4

The way you do this is another column in your table called parent_id or something like that.

The original message will have parent_id of NULL.

Then, when someone sends a response to the message, the original message identifier is sent to the parent_id column of the new message. For instance:

 id text parent_id created_at ============================================ 1 'Lorem ipsum' null [time] 2 'Lorem ipsum' 1 [time] 3 'Lorem ipsum' 1 [time] 

You can even go further and have nested answers:

 id text parent_id created_at ============================================ 1 'Lorem ipsum' null [time] 2 'Lorem ipsum' 1 [time] 3 'Lorem ipsum' 2 [time] 

In the latter case, you probably need some kind of recursive function to get all the attached messages; The first way is simpler.

Answer 5

The best way to post a unique identifier; to add uniqid (rand ()).