1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

MySQL and adding unique INT value when not using AUTO_INCREMENT

Discussion in 'MySQL' started by xms, Aug 8, 2017.

  1. #1
    I have a MySQL table. I must be able to add unique INT values for speed which is not an AUTO_INCREMENT column.
    SEMrush
    
    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.
     
    xms, Aug 8, 2017 IP
    SEMrush
  2. Blank ™

    Blank ™ Well-Known Member

    Messages:
    223
    Likes Received:
    18
    Best Answers:
    6
    Trophy Points:
    110
    #2
    
    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.
     
    Blank ™, Aug 8, 2017 IP
  3. xms

    xms Active Member

    Messages:
    169
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    53
    #3
    It seems to me that it is not 100% sure that the value of speed will be unique.
     
    xms, Aug 8, 2017 IP
  4. Blank ™

    Blank ™ Well-Known Member

    Messages:
    223
    Likes Received:
    18
    Best Answers:
    6
    Trophy Points:
    110
    #4
    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.
     
    Blank ™, Aug 9, 2017 IP
  5. xms

    xms Active Member

    Messages:
    169
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    53
    #5
    In case there are two inserts exactly at the same time.
     
    xms, Aug 9, 2017 IP
  6. Blank ™

    Blank ™ Well-Known Member

    Messages:
    223
    Likes Received:
    18
    Best Answers:
    6
    Trophy Points:
    110
    #6
    That could cause some issues, that's for sure.
     
    Blank ™, Aug 9, 2017 IP
  7. xms

    xms Active Member

    Messages:
    169
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    53
    #7
    So, I'm still looking for help...
     
    xms, Aug 9, 2017 IP
  8. Blank ™

    Blank ™ Well-Known Member

    Messages:
    223
    Likes Received:
    18
    Best Answers:
    6
    Trophy Points:
    110
    #8
    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.
     
    Blank ™, Aug 9, 2017 IP
  9. xms

    xms Active Member

    Messages:
    169
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    53
    #9
    Well, how can I control it?
     
    xms, Aug 9, 2017 IP
  10. Blank ™

    Blank ™ Well-Known Member

    Messages:
    223
    Likes Received:
    18
    Best Answers:
    6
    Trophy Points:
    110
    #10
    Restructure your database so it'd support something like that.
     
    Blank ™, Aug 9, 2017 IP
  11. qwikad.com

    qwikad.com Illustrious Member Affiliate Manager

    Messages:
    5,483
    Likes Received:
    1,020
    Best Answers:
    19
    Trophy Points:
    400
    #11
    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):
     
    Last edited: Aug 9, 2017
    qwikad.com, Aug 9, 2017 IP
  12. xms

    xms Active Member

    Messages:
    169
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    53
    #12
    Yes, I have been thinking that. Maybe the best way, or do you have other ideas?
     
    xms, Aug 9, 2017 IP
  13. qwikad.com

    qwikad.com Illustrious Member Affiliate Manager

    Messages:
    5,483
    Likes Received:
    1,020
    Best Answers:
    19
    Trophy Points:
    400
    #13
    I don't. I am using it myself, actually, I've never run into an issue of having duplicate entries.
     
    qwikad.com, Aug 9, 2017 IP
  14. xms

    xms Active Member

    Messages:
    169
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    53
    #14
    Maybe I will use MySQL MICROSECOND() function for creating a "random" integer.
     
    xms, Aug 9, 2017 IP
  15. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,626
    Likes Received:
    719
    Best Answers:
    151
    Trophy Points:
    420
    #15
    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?
     
    PoPSiCLe, Aug 10, 2017 IP
  16. xms

    xms Active Member

    Messages:
    169
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    53
    #16
    How would you dupblicate the auto-increment ID column?
     
    xms, Aug 10, 2017 IP
  17. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,626
    Likes Received:
    719
    Best Answers:
    151
    Trophy Points:
    420
    #17
    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
     
    PoPSiCLe, Aug 10, 2017 IP