How to start a primary key with a specific letter?

Question

Here I use MySQL, and I want my primary key to start with a letter, such as D000. Then, every time I enter a new record, the primary key is automatically incremented like this:

D001 D002 D003.

How can i do this?

Answer 1

You cannot AUTO_INCREMENT column whose type is VARCHAR .

What you can do is do BIGINT and AUTO_INCREMENT , and whenever you need it as a String , you can add it using the letter 'D' , for example:

 Long dbKey = ...; String key = "D" + dbKey; 

You can create a stored procedure for this to set the line with auto-increments as the default value for this column, but this is just not worth the hassle. Plus, working with numbers is always faster and more efficient than working with strings.

Answer 2

Not sure why you need this, but you can add D AFTER you have retrieved the data ( String id = "D" + autoIncId; ).

You cannot insert a line or anything into the auto-increment field, and I do not see that this can be useful (all entries will have D, so no one has).

If you want to declare a default row, you can add a boolean column named DEFAULT .

 while(rs.next()){ String id = rs.getBoolean("DEFAULT")?"D":"ND"; id+=rs.getLong(1); } 

EDIT

According to your comment, I understand that you want to select max ID and add 1 to it. Then it’s good to use the auto-increment field in your database, and it should be a number type (INTEGER, BIGINT ...).

IT IS FORBIDDEN to add "D" to your primary key, it just won’t work the way you want. Auto increment takes the last inserted identifier and adds 1 to it. If your last identifier is "D3", appendix 1 has the same meaning as appendix 4 to "apple". You are using different types.

There is no way for SQL or any other programming language to understand that if you add 1 to "D3", it should become "D4". What you need to do is get rid of this D (whose purpose I still don't understand).

Answer 3

I am not sure if I received your question correctly, but should the following not work?

 CREATE TRIGGER myTrigger BEFORE INSERT ON myTable FOR EACH ROW BEGIN SET NEW.myCustomId = COALESCE('D', RPAD('0',3,NEW.id)); END 

for this case you NEED the “normal” primary key column.

Answer 4

Two ideas.

• (Useless IMHO) I think Maria DB has virtual columns, although MySQL, I think, is not. But you have views. Thus, you can do the usual INT, AUTOINCREMENT and in the view have a computed column connecting your key.

• For different tables, you can use different ranges of numbers .

   ALTER TABLE debtors AUTO_INCREMENT=10000; ALTER TABLE creditors AUTO_INCREMENT=30000; ALTER TABLE guests AUTO_INCREMENT=50000; 

  This is admittedly a lame decision, but it can do. I think that such a difference may be what you are striving for.

Answer 5

Yo may try to do this aberration at your own risk:

 INSERT INTO table (id, a, b, c) VALUES ( fn_get_key( LAST_INSERT_ID("table_name ") +1), "a", "b", "c"); 

Where fn_get_key is a function that converts a number to the desired string and executes:

 ALTER TABLE table_name AUTO_INCREMENT = start_value; 

In any case, I do not recommend your approach. Numeric strings are faster and easier to sort. You can always create a view that transforms the identifier or uses the o logic to go from "D001" to "1". The front key and the unity of compliance will be more complicated and expensive.