How to generate number sequence starting with birth date in MySQL
I need to populate my database with test data. I have a table "Citizen" with columns:
--------------------- | CITIZEN | --------------------- | id INT | | name VARCHAR | | city VARCHAR | | birthDate DATETIME| | pesel INT | --------------------
The las column - PESEL, is a personal identity number used in Poland. It has 11 digits and first six of them are the date of birth written from the end like this: 910111 which means 11.01.1991 (11th of January). Numbers from 7-10 may be random, but the last digit is a checksum calculated by the following formula:
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.
Now I have all my other columns filled accept for PESEL. I need about 100 000 records so I generate them in a loop rather then do it manually. However I don't know how to determine each digit of a number and connect it with birth dates that I have in birthDate column... I would appreciate any help.
If you can use an external programming language you can use (for example) this Ruby library to generate a correct PESEL number for each row.
On the other hand, if you want to do this in MySQL alone you could use something like the following function (based on the Ruby library mentioned above):
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
and then use that to update the column like this:
UPDATE CITIZEN SET pesel = Pesel(DATE_FORMAT(birthDate,"%y%m%d"))
Note that I now used a
VARCHAR(11) as the datetype for the
pesel column, this was a bit easier to figure out the function. If you want to play with the SQL you can use this SQL Fiddle.
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]