根据MySQL中的另一列创建一个计算列 [英] Create a computed column based on another column in MySQL

问题描述

我的表中有2列： varchar（8）和 int 。

我想要自动增量 int列，当我我想将值复制到 varchar（8）列中，但是用0填充直到长度为8个字符，例如，如果 int column 增加到 3 ， varchar（8）列会包含'00000003'。

我的两个问题是，当 varchar（8）列变为 '99999999'因为我不想重复？

如何在MySQL中执行此操作？

如果我的值可以介于 00000000 到 99999999 之间，我可以有多少个值

这是我创建一个随机的8个字符的字符串和检查重复的MySQL的替代方法。我认为这是一个更好的方法，并将允许更多的值。

解决方案

因为您的格式化列依赖于，可以从id列中导出，您的表格设计违反了 3NF 。

要么创建一个包含您的派生列的视图（请参见 sqlfiddle ）：
$ b

  CREATE VIEW myview AS 
 SELECT *，substring（cast（100000000 + id AS CHAR（9）），2）AS formatted_id 
 FROM mytable 
  

在 10000000 中增加，那么它总是8位数字：

  ALTER TABLE mytable AUTO_INCREMENT = 10000000; 
  

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;

这篇关于根据MySQL中的另一列创建一个计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！