mysql中将某个字段做计算,根据MySQL中的另一列创建一个计算列

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.

解决方案

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;