2

I have a 2 columns in my table: a varchar(8) and an int.

I want to auto-increment the int column and when I do, I want to copy the value into the varchar(8) column, but pad it with 0's until it is 8 characters long, so for example, if the int column was incremented to 3, the varchar(8) column would contain '00000003'.

My two questions are, what happens when the varchar(8) column gets to '99999999' because I don't want to have duplicates?

How would I do this in MySQL?

If my values can be between 00000000 to 99999999, how many values can i have before I run out?

This is my alternative approach to just creating a random 8 character string and checking MySQL for duplicates. I thought this was a better approach and would allow for a greater number of values.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
Xaisoft
  • 45,655
  • 87
  • 279
  • 432

2 Answers2

5

Because your formatted column depends upon, and is derivable from, the id column, your table design violates 3NF.

Either create a view that has your derived column in it (see this in sqlfiddle):

CREATE VIEW myview AS
SELECT *, substring(cast(100000000 + id AS CHAR(9)), 2) AS formatted_id
FROM mytable

or just start your auto-increment at 10000000, then it will always be 8 digits long:

ALTER TABLE mytable AUTO_INCREMENT = 10000000;
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • I need one column to be a varchar(8) and it is my understanding that you can't auto-increment an INT, so I thought the next best solution would be to auto-increment another int field and the left pad the varchar field with 0's until 8 characters long. I also thhought between `00000000` and `99999999`, I should get a wide range of numbers? – Xaisoft May 09 '13 at 15:08
  • Right. More generally, you shouldn't have two columns that represent the same physical data. Either use the `int` as your key and if you really need a string-representation of the value, create it in your presentation layer; or, use the `varchar(8)` version as your primary key and do away with the `int`. – James Cronen May 09 '13 at 15:08
  • When a record is inserted into the table, a trigger is fired which passes xml data with the varchar(8) column to another system, so that is why I need the varchar(8). Would another option be to CAST the auto-incremented INT to a varchar(8) in the xml? If this is possible, I guess this would avoid having two fields represent the same value. – Xaisoft May 09 '13 at 15:10
  • But if I start the auto-increment at 0, then I will get more values than if I just started it at 10000000? – Xaisoft May 09 '13 at 15:19
  • You only get 10% less values - you still get `10000000-99999999`. Even then, if you exceed 99999999, the number will roll over to a 9-digit number and you're hosed. So I image you must be expecting a lot less than 99999999 inserts, so 10% less probably isn't going to be a problem. The gain is simplicity - always a good thing. – Bohemian May 09 '13 at 15:22
  • Only 10%, seemed like it would be more, is there way I can start the auto-increment directly in the create table instead of in alter table. Here is my example: http://sqlfiddle.com/#!2/ac8d7/1/0 – Xaisoft May 09 '13 at 15:40
  • Also, is is possible to CAST or CONVERT into a VARCHR(8)? – Xaisoft May 09 '13 at 15:44
  • Unfortunately it is not possible to cast to varchar in mysql. But do you need that? You want exactly 8 characters, so char(8) is OK. Also I doubt there is an application language that distinguishes char() from varchar(). I can't see why char() would not work for you. – Bohemian May 09 '13 at 21:24
1

Simple, if the column is unique, it will throw an exception telling that the value already do exists. But if not unique, after 99999999 you'll get error message that the value is truncated.

Alternatives, why not use INT AUTO_INCREMENT? or a custom ID with a combination of date/time, eg

YYMMDD-00000

This will have a maximum record of 99999 records per day. It will reset on the next day.

John Woo
  • 258,903
  • 69
  • 498
  • 492