3

Is it possible to attach a letter in front of an auto-increment in MySQL. I have several tables in my database, some of which have unique id's created by auto-increment. I want to be able to distinguish between the auto-generated numbers by a letter at the front.

This is not a feature which is absolutely required but it would just help making small tasks easy.

Ben
  • 51,770
  • 36
  • 127
  • 149
  • Just to clarify: What you want is for all the keys in tableA to be of the form A123, all the keys in tableB to be of the form B123, and so forth, so that the keys become globally unique in the database? – Russell Zahniser Mar 10 '12 at 16:55

3 Answers3

4

You could create views for the tables that need distinctive letters in front of their ID values, and read the tables through the views:

CREATE VIEW VTableA
AS
SELECT
  CONCAT('A', ID) AS ID,
  other columns
FROM TableA

Same for other tables.

Andriy M
  • 76,112
  • 17
  • 94
  • 154
3

Unfortunately you cannot do that. At least not in sql. An autoincrement field is of integer type, so adding a letter there violates the constraint.

You can have a look to the link below for some sort of a solution to this problem.

MySQL auto increment plus alphanumerics in one column

I hope that this can guide you to the right direction.

Community
  • 1
  • 1
Aristotelis Kostopoulos
  • 835
  • 4
  • 15
0

The best answer probably depends on what you mean by an alphanumeric ID. Does the alpha part increment in some way, and if so, what are the rules for that? If the alpha part is static, then you don't even need it in the DB: just prepend it to the numeric ID when you output it (perhaps using [s]printf() or similar functions to prepend zeroes so that it's a fixed length?). Without know the full requirement, though, we're all just speculating

Atul Stha
  • 1,404
  • 8
  • 23
  • 46