dSbsaJLp

1DROP FUNCTION IF EXISTS `RandString`;
2
3DELIMITER $$
4
5CREATE FUNCTION `RandString`(length SMALLINT(3)) RETURNS varchar(100) CHARSET utf8
6begin
7    SET @returnStr = '';
8    SET @allowedChars = 'ABC';
9    SET @i = 0;
10
11    WHILE (@i < length) DO
12        SET @returnStr = CONCAT(@returnStr, substring(@allowedChars, FLOOR(RAND() * LENGTH(@allowedChars) + 1), 1));
13        SET @i = @i + 1;
14    END WHILE;
15
16    RETURN @returnStr;
17END$$
18
19DELIMITER ;
20
21DROP TABLE IF EXISTS `Unique`;
22
23CREATE TABLE IF NOT EXISTS `Unique` (
24  `idUnique` VARCHAR(8) NOT NULL DEFAULT '',
25  PRIMARY KEY (`idUnique`));
26
27DROP TRIGGER IF EXISTS Unique_beforeInsert;
28
29DELIMITER $$
30
31CREATE TRIGGER Unique_beforeInsert
32  BEFORE INSERT ON `Unique`
33  FOR EACH ROW
34  BEGIN
35    SET @uniqueOther = 1;
36    SET @tries = 10;
37    WHILE (@uniqueOther > 0) DO 
38      SET NEW.idUnique = RANDSTRING(1);
39      SET @uniqueOther = (SELECT COUNT(*) FROM `Unique` WHERE `idUnique` = NEW.idUnique);
40      SET @tries = @tries - 1;
41      IF (@tries <= 0) THEN
42          SIGNAL SQLSTATE '45000';
43      END IF;
44    END WHILE;
45  END;$$
46
47DELIMITER ;