How to generate number sequence starting with birth date in MySQL


Problem :

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.



Solution :

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.


Mysql Tutorials

Mysql Howto..

How can I combine 2 columns in sql table to search for full names

How to query, then process a large quantity in somewhat short time frame

How to save a HTML table in MySQL database - efficient way to do this? [closed]

How To Store User Purchases in MYSQL?

how to synchronize two databases of different servers?

How to select from MySQL where Table name is Variable

How to implement pessimistic locking in a php/mysql web application?

PHP-CodeIgniter: How to get the corresponding html table row values to be deleted by Javascript

how to create a search form in php and mysql with pagination?

How to make inner join with two conditions in MySQL

How to create Category from one table Colums in MySQL Result [closed]

How to select data from MySQL table where a column is NULL

How to do multi-table joins in MySQL involving composite foreign keys?

How to make join query for mysql

MySQL show code if table > 0

How to access online mysql database in android?

How to find out the count of one column in multiple group by using MySQL?

How to find out the username and password for mysql database

Need advice on how to optimize this database further

How to filter MySQL results to display LIMIT 1 on per user basis?

how to store public key mysql using Java?

How to get the max value from a column with alphanumeric strings?

How to store NSDate to DATETIME with **TIME ZONE**?

MYSQL Query - how to sum data by total, current year and last 12 months in the same query (1 sum by column)?

How to use CASE plus JOIN plus GROUP BY into a single MySQL statement

How can I ignore time for NOW()?

How to parse a mysql slow query log into something useful?

PHP and MYSQL How to View Multiple Order Information in the Other Page

PHP, PDO, MySQL & InnoDB, how to update/delete records with FK?

How to get hold of Amazon MySQL RDS certificates