I need a trigger to create id's in my sql database with a string and some zeros
I'm currently using this trigger which adds id's with 3 zeros and two zeros and then the id from the sequences table.
BEGIN INSERT INTO sequences VALUES (NULL); SET NEW.deelnemernr = CONCAT('ztmr16', LPAD(LAST_INSERT_ID(), 3, '0')); END
I changed the 3 to 4 but then it didn't increment the id anymore, resulting in and multiple id error. It stayed at ztmr16000. So what can I do to add more zeros and still get the id from the sequencestable?
The MySQL LPAD function limits the number of characters returned to the specified length.
The specification is a bit unclear, what you are trying to achieve.
If I need a fixed length string with leading zeros, my approach would be to prepend a boatload of zeros to my value, and then take the rightmost string, effectively lopping off extra zeros from the front.
To format a non-negative integer value val into a string that is ten characters in length, with the leading characters as zeros, I'd do something like this:
As a demonstration:
SELECT RIGHT(CONCAT('000000000','123456789'),10) --> 0123456789 SELECT RIGHT(CONCAT('000000000','12345'),10) --> 0000012345
Also, I'd be cognizant of the maximum length allowed in the column I was populating, and be sure that the length of the value I was generating didn't exceed that, to avoid data truncation.
If the value being returned isn't be truncated when it's inserted into the column, then what I think the behavior you observe is due to the value returned from LAST_INSERT_ID() exceeding 1000.
Note that for a non-negative integer value val, the expression
will allow at most 1000 distinct values. LPAD (as I noted earlier) restricts the length of the returned string. In this example, to three characters. As a demonstration of the behavior:
SELECT LPAD( 21,3,'0') --> 021 SELECT LPAD( 321,3,'0') --> 321 SELECT LPAD( 54321,3,'0') --> 543 SELECT LPAD( 54387,3,'0') --> 543
There's nothing illegal with doing that. But you're going to be in trouble if you depend on that to generate "unique" values.
As stated, the specification ...
"adds id's with 3 zeros and two zeros and then the id from the sequences table."
is very unclear. What is it exactly that you want to achieve? Consider providing some examples. It doesn't seem like there's an issue concatenating something to those first five fixed characters. The issue seems to be with getting the id value "formatted" to your specification
This is just a guess of what you are trying to achieve:
id value formatted return -------- ---------------- 1 0001 9 0009 22 0022 99 0099 333 0333 4444 4444 55555 55555 666666 666666
You could achieve that with something like this:
BEGIN DECLARE v_id BIGINT; INSERT INTO sequences VALUES (NULL); SELECT LAST_INSERT_ID() INTO v_id; IF ( v_id <= 9999 ) THEN SET NEW.deelnemernr = CONCAT('ztmr16',LPAD(v_id,4,'0')); ELSE SET NEW.deelnemernr = CONCAT('ztmr16',v_id); END IF; END