MySQL and adding unique INT value when not using AUTO_INCREMENT

I have a MySQL table. I must be able to add unique INT values for speed which is not an AUTO_INCREMENT column.

CREATE TABLE ki
(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
  , comp_id INT(10) UNSIGNED NOT NULL
  , speed INT(4) UNSIGNED NOT NULL DEFAULT 0
  , position INT(4) UNSIGNED NOT NULL DEFAULT 0

  , PRIMARY KEY (id)

  , UNIQUE INDEX (comp_id, speed, position)
  , INDEX (comp_id)

  , FOREIGN KEY (comp_id)
  REFERENCES competitions (id)
  ON DELETE NO ACTION
  ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET latin1 COLLATE latin1_swedish_ci;

Code (SQL):

I want to insert new rows. comp_id is always 1, position is always 0, and speed must always be MAX(speed) + 1.

So, let's assume that I want speed to be 3. speed must be unique.

INSERT INTO ki (comp_id, speed, position) VALUES (1, 3, 0)

Code (SQL):

If the value 3 already exists, I would like speed to be 4.

I do not want to modify the existing rows.

How could I do this on my SQL query? As told, speed must be unique.

 

INSERT INTO ki (comp_id, speed, position) VALUES (1, (SELECT MAX(speed) FROM ki K) + 1, 0);

Code (sql):

Something like that. Works just fine on 5.7.

 

It seems to me that it is not 100% sure that the value of speed will be unique.

 

And the reason would be? It takes the max speed value from the same table and increases it by one. There's really way it can not be unique.

 

In case there are two inserts exactly at the same time.

 

That could cause some issues, that's for sure.

 

So, I'm still looking for help...

 

You won't get any. Either MySQL itself is handling the numbering or you will definitely at some point could run into some issues with the number not being unique as it is you controlling it, not the server itself.

 

Well, how can I control it?

 

Restructure your database so it'd support something like that.

 

If it just has to be a unique something, have you thought of using random codes? You can eliminate the letters and use the numbers only.

$speed = substr(str_shuffle(str_repeat("0123456789ABCDIFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz", 15)), 0, 15);

Code (markup):

SET speed = '$speed'

Code (markup):

 

Yes, I have been thinking that. Maybe the best way, or do you have other ideas?

 

I don't. I am using it myself, actually, I've never run into an issue of having duplicate entries.

 

Maybe I will use MySQL MICROSECOND() function for creating a "random" integer.

 

WHY do you need unique entries for speed? It seems to me that the actual value of this column is irrelevant, as long as it is unique? Then why don't you just either duplicate the auto-increment ID column, or just USE that ID-column instead?

 

How would you dupblicate the auto-increment ID column?

 

Via a trigger?

AFTER INSERT ON `table_name`
           FOR EACH ROW BEGIN
            UPDATE `table_name` SET `speed` = NEW.id WHERE id = NEW.id
           END");

Code (markup):

Something like that. Basically, when you insert a new entry into the database, you run a trigger that updates the speed-column with the value from the id-column. This is untested, so I can't guarantee that this is 100% syntactically correct, but it is more or less correct