CODEWITHSUNDEEP
mysqlsql
gator
gator

Reputation: 3523

Make unique string of characters/numbers in SQL

I have a table someTable with a column bin of type VARCHAR(4). Whenever I insert to this table, bin should be a unique combination of characters and numbers. Unique in this sense meaning has not appeared before in the table in another row.

bin is in the form of AA00, where A is a character A-F and 0 is a number 0-9.

Say I insert to this table once: it should come up with a bin value which doesn't appear before. Assuming the table was empty, the first bin could be AA11. On second insertion, it should be AA12, and then AA13, etc.

AA00, AA01, ... AA09, AA10, AA11, ... AA99, AB00, AB01, ... AF99, BA00, BA01, ... FF99

It doesn't matter this table can contain only 3,600 possible rows. How do I create this code, specifically finding a bin that doesn't already exist in someTable? It can be in order as I've described or a random bin, as long as it doesn't appear twice.

CREATE TABLE someTable (
    bin VARCHAR(4),
    someText VARCHAR(32),
    PRIMARY KEY(bin)
);

INSERT INTO someTable
    VALUES('?', 'a');
INSERT INTO someTable
    VALUES('?', 'b');
INSERT INTO someTable
    VALUES('?', 'c');
INSERT INTO someTable
    VALUES('?', 'd');

Alternatively, I can use the below procedure to insert instead:

CREATE PROCEDURE insert_someTable(tsomeText VARCHAR(32))
    BEGIN
    DECLARE var (VARCHAR(4) DEFAULT (
        -- some code to find unique bin
    );
    INSERT INTO someTable
        VALUES(var, tsomeText);
    END

A possible outcome is:

+------+----------+
|  bin | someText |
+------+----------+
| AB31 |    a     |
| FC10 |    b     |
| BB22 |    c     |
| AF92 |    d     |
+------+----------+

Upvotes: 0

Views: 314

Answers (1)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

As Gordon said, you will have to use a trigger because it is too complex to do as a simple formula in a default. Should be fairly simple, you just get the last value (order by descending, limit 1) and increment it. Writing the incrementor will be somewhat complicated because of the alpha characters. It would be much easier in an application language, but then you run into issues of table locking and the possibility of two users creating the same value.

A better method would be to use a normal auto-increment primary key and translate it to your binary value. Consider your bin value as two base 6 characters followed by two base 10 values. You then take the id generated by MySQL which is guaranteed to be unique and convert to your special number system. Calculate the bin and store it in the bin column.

To calculate the bin:

  1. Step one would be to get the lower 100 value of the decimal number (mod 100) - that gives you the last two digits. Convert to varchar with a leading zero.
  2. Subtract that from the id, and divide by 100 to get the value for the first two digits.
  3. Get the mod 6 value to determine the 3rd (from the right) digit. Convert to A-F by index.
  4. Subtract this from what's left of the ID, and divide by 6 to get the 4th (from the right) digit. Convert to A-F by index.
  5. Concat the three results together to form the value for the bin.

You may need to edit the following to match your table name and column names, but it should so what you are asking. One possible improvement would be to have it cancel any inserts past the 3600 limit. If you insert the 3600th record, it will duplicate previous bin values. Also, it won't insert AA00 (id=1 = 'AA01'), so it's not perfect. Lastly, you could put a unique index on bin, and that would prevent duplicates.

DELIMITER $$

CREATE TRIGGER `fix_bin` 
BEFORE INSERT ON `so_temp`
FOR EACH ROW
BEGIN
    DECLARE next_id INT;
    SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='so_temp');
    SET @id = next_id;
    SET @Part1 = MOD(@id,100);
    SET @Temp1 = FLOOR((@id - @Part1) / 100);
    SET @Part2 = MOD(@Temp1,6);
    SET @Temp2 = FLOOR((@Temp1 - @Part2) / 6);
    SET @Part3 = MOD(@Temp2,6);

    SET @DIGIT12 = RIGHT(CONCAT("00",@Part1),2);
    SET @DIGIT3 = SUBSTR("ABCDEF",@Part2 + 1,1);
    SET @DIGIT4 = SUBSTR("ABCDEF",@Part3 + 1,1);
    SET NEW.`bin` = CONCAT(@DIGIT4,@DIGIT3,@DIGIT12);
END;
$$

DELIMITER ;

Upvotes: 1

Related Questions