如何在MySQL中生成从出生日期开始的数字序列

Question

我需要用测试数据填充我的数据库。我有一张列有“公民”的桌子：

---------------------
| CITIZEN           |
---------------------
| id   INT          |
| name VARCHAR      |
| city VARCHAR      |
| birthDate DATETIME|
| pesel INT         |
--------------------

拉斯列它有11位数字，前6位是从结尾写成的出生日期: 910111，即1991年1月11日(1月11日)。7-10的数字可能是随机的，但最后一个数字是由下列公式计算的校验和：

 1*a + 3*b + 7*c + 9*d + 1*e + 3*f + 7*g + 9*h + 1*i + 3*j
 where letters from a to j stand for each digit of PESEL number.

现在我把我所有的专栏都填上了。我需要大约10万条记录，所以我在一个循环中生成它们，而不是手动地去做。但是，我不知道如何确定一个数字的每一个数字，并将其与我在birthDate列中的出生日期联系起来。我很感谢你的帮助。

Answer 1

如果可以使用外部编程语言，则可以使用(例如) 这个Ruby库为每一行生成正确的PESEL号。

另一方面，如果您只想在MySQL中这样做，您可以使用如下函数(基于上面提到的Ruby库)：

CREATE FUNCTION Pesel(birthDate VARCHAR(6)) RETURNS VARCHAR(11)
BEGIN 
    DECLARE result VARCHAR(11);
    SELECT CONCAT(A, B, C, D, E, F, G, H, I, J,
           IF(((1*A + 3*B + 7*C + 9*D + 1*E + 3*F + 7*G + 9*H + 1*I + 3*J) % 10) = 10
             , 0
             ,(1*A + 3*B + 7*C + 9*D + 1*E + 3*F + 7*G + 9*H + 1*I + 3*J) % 10)) AS pesel
    INTO result
    FROM (SELECT SUBSTR(birthDate,1,1) AS A,
                 SUBSTR(birthDate,2,1) AS B,
                 SUBSTR(birthDate,3,1) AS C,             
                 SUBSTR(birthDate,4,1) AS D,             
                 SUBSTR(birthDate,5,1) AS E,             
                 SUBSTR(birthDate,6,1) AS F,                          
                 FLOOR(RAND()*10) AS G, 
                 FLOOR(RAND()*10) AS H,
                 FLOOR(RAND()*10) AS I,
                 FLOOR(RAND()*10) AS J) AS tmp;
    return result;
END

然后使用它来更新列如下：

UPDATE CITIZEN
SET pesel = Pesel(DATE_FORMAT(birthDate,"%y%m%d")) 

注意，我现在使用一个VARCHAR(11)作为pesel列的日期类型，这有点容易理解这个函数。如果您想使用SQL，可以使用此SQL Fiddle。

Answer 2

ebo发布的解决方案很棒，但是如果数字为% 10 = 10，则不需要使用。不管“数字”的值是多少--没有办法得到结果等于10。

我在postgresql中遇到了同样的问题，所以有一个select，它从表"sometable“返回所有bith_dates的pesel。birth_date格式为YYYY。

 SELECT CONCAT(A || B || C || D || E || F || G || H || I || J, 
((1*A::int + 3*B::int + 7*C::int + 9*D::int + 1*E::int + 3*F::int + 7*G::int 
+ 9*H::int + 1*I::int + 3*J::int) % 10)::text
)AS "PESEL" FROM             
(SELECT SUBSTRING(bith_date::text,3,1) AS A,
             SUBSTRING(bith_date::text,4,1) AS B,
             SUBSTRING(bith_date::text,6,1) AS C,             
             SUBSTRING(bith_date::text,7,1) AS D,             
             SUBSTRING(bith_date::text,9,1) AS E,             
             SUBSTRING(bith_date::text,10,1) AS F,                          
             FLOOR(RANDOM()*10)::text AS G, 
             FLOOR(RANDOM()*10)::text AS H,
             FLOOR(RANDOM()*10)::text AS I,
             FLOOR(RANDOM()*10)::text AS J
         FROM sometable) AS tmp