MySQL intelligent default value

I'm adding stuff to a MySQL table, and each item has a position that it shows up in my system like a question number.

I could find out what the largest position is before adding a new question, +1 it, and then add it - but I was wondering if there's a more intelligent way that doesn't require a second query.

Something like INSERT INTO questions (id, position) values (0, MAX(position)).

This field is not the primary key, auto_increment is of no use to this situation.

I cannot use position as the key, because the key relates to many other things, and the position can be changed at any time.

I am a pretty confident MySQL query writer, so please don't offer any suggestions other than the question asked - I know of plenty of alternatives, thi开发者_开发问答s is just a syntax question.

I'm sure you get my drift!

Cheers.

Use auto_increment like most other folk do?

This is the intelligent way because it concurrency safe.

Best way would be to change the field properties within MySQL, meaning let MySql do the work without providing input.

TABLE TableName CHANGE position position INT( 11 ) NOT NULL AUTO_INCREMENT

Now when inserting, do not provide any values for this field.

INSERT INTO questions (var1, var2) values (val1,val2)

MySQL will automatically increment the position value. You can set current counter as well as reset if need be. This way you are not running any quarries at all.

something like this should work

INSERT INTO questions (id, position) SELECT 0, MAX(position)+1 FROM questions;

Obviously, you will need to handle the 0 value, as in set it for something appropriate, i just copied that value off your example.

You could also create a stored procedure to use in the query, or even have a trigger which determines the MAX(position), adds one and stores it in your newly created row.

Quite alot of options there, I would go for the above solution though (with a index on position)

There's no way to set a default value to a specific calculation, it seems.