Procedure SQL to update table

Version imprimable

Bonjour,
Je cherche à créer une procédure/requête sous MySQL, qui permette de générer un code à partir des champs date de naissance et codesexe.
Par exemple, j'ai la table Personne:
Personne(id, datenaiss, codesexe, codeunique);
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE TABLE IF NOT EXISTS `personne` ( `id` int(11) DEFAULT NULL, `datenaiss` date DEFAULT NULL, `codesexe` int(11) DEFAULT NULL, `codeunique` varchar(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO `personne` (`id`, `datenaiss`, `codesexe`, `codeunique`) VALUES (1, '2000-05-18', 1, ''), (2, '2000-05-18', 1, ''), (3, '2003-10-05', 0, ''), (4, '2003-10-05', 1, ''), (5, '2003-10-05', 0, '');
le codeunique est composé par année de la date de naissance suivie de codesexe puis 6 chiffres incrémentés automatiquement.
J'ai créé cette requête:
Code:

1 2 3 4 UPDATE Personne SET codeunique=CONCAT( YEAR(datenaiss), codesexe, RPAD(codeunique,10,'0'));
Mais l'incrémentation automatique ne marche pas.
On doit obtenir un résultat comme suit:
http://s2.postimg.org/efbkx35xl/codeunique.png
Merci de m'aider

27/05/2013, 10h54
aieeeuuuuu
Bonjour,

à première vue, je dirai :
Code:

1 2 3 4 5 6 UPDATE Personne SET codeunique=RPAD( CONCAT(YEAR(datenaiss),codesexe) ,10 ,'0');
Mais, je ne vois pas en quoi cela vous assure un code unique ?

Êtes vous sûr de n'avoir jamais deux personnes du même sexe nées le même jour ?
27/05/2013, 14h25
fbmhm

Bonjour;

On ne prend pas le jour de naissance en considération.

Le codeunique a gérer sera composé par l'année de naissance, codesexe puis un nombre incrémental compose de 6 chiffres:
c-a-d pr l'an 2000 et le sexe masculin (codesexe=1), on peut avoir un million de codeunique allant de 20001000000 à 20001999999.

Le problème c'est comment incrémenter les chiffres en vert automatiquement.
30/05/2013, 00h36
ericd69
salut,

l'idée est d'avoir une table où tu stockes ton compteur

de bien voir le niveau de transaction que tu veux en fonction de ton besoin et d'appliquer dans une transaction:
- lire la valeur pour l'utiliser dans ton update ici
- faire un update pour incrémenter la valeur dans la table de stockage pour la prochaine utilisation...
selon le niveau d'isolation l'odre de ses action peut changer...

J'ai programmé ça en PHP, mais le problème c'est après chaque update/insert il faut déclencher le fichier PHP.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
<?php
$db = mysql_connect('localhost', 'root', '');
mysql_select_db('personnes',$db);
$reqpersonnes="select * from personne WHERE codeunique=''";
$listpersonnes = mysql_query($reqpersonnes) or die('Erreur SQL !<br>'.$reqpersonnes.'<br>'.mysql_error());
 while ($row = mysql_fetch_array($listpersonnes)) {
$date_explosee = explode("-", $row['datenaiss']);
$annee = $date_explosee[0];
$part1=substr($annee, 1, 3).$row['codesexe'];
$reqpart2="select max(codeunique) from personne WHERE codeunique LIKE '$part1%'";
$listpart2 = mysql_query($reqpart2) or die('Erreur SQL !<br>'.$reqpart2.'<br>'.mysql_error());
$nbre= mysql_num_rows($listpart2);
$res= mysql_fetch_array($listpart2);
if($nbre>0){
$part2='0000'.substr($res[0], 4, 6)+1;
$zero=6-strlen($part2);
$p='';
for($i=0;$i < $zero;$i++) {
$p=$p.'0';
}
}
else{
$p='000000';
}
 
$codeunique=$part1.$p.$part2;
$id=$row['id'];
$sqlcodeunique="Update personne set codeunique='$codeunique' WHERE id='$id'";
$update = mysql_query($sqlcodeunique) or die('Erreur SQL !<br>'.$sqlcodeunique.'<br>'.mysql_error());
$p='';
$part1='';
$part2='';
$codeunique='';
$sqlcodeunique='';
}
?>

une transaction se définit par ce qui se passe entre un start transaction et soit:

commit pour la valider...
rollback pour l'annuler...

en utilisant un select ... for update, par exemple, tu vas poser un verrou jusqu'à l'update sur la valeur lue
ce qui met en attente les transactions parallèles jusqu'à l'update qui fait l'incrément de la valeur

dans la table tu dois soit avoir un mécanisme lors du changement d'année et tenant compte du sexe... le plus simple est d'avoir un compteur (donc une ligne par année)... ce qui te permet en plus d'avoir le décompte par an directement en plus...
Code:

1 2 3 4 5 6 7 create table compteurs( id smallint unsigned auto_increment not null annee smallint unsigned, compteur smallint, sexe bool, contraint pk_compteurs primary key(id) )engine=innodb;
tu la remplis pour avoir tous les cas de figure initiaux...

tu crées ensuite une procédure stockée pour générer tes entrées en une fois:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 delimiter $$ drop procedure if exists cree_personne$$ create procedure cree_personne(in s bool, in d date) begin declare c smallint; start transaction; select compteur into c from compteurs where sexe=s and annee=year(d) for update; insert into personnes(`id`, `datenaiss`, `codesexe`, `codeunique`) values (null,d,s,concat(year(d),s,c)); update compteur set compteur=compteur+1 from compteurs where sexe=s and annee=year(d); commit; end$$ delimiter ;
l'appel se faisant par:

Code:

call cree_personne(0,'2000-03-02');

pour te connecter à mysql n'utilise plus les fonctions du genre mysql_xxx(), tu dois utiliser pdo car l'autre extension va être virée de php...

il te reste à te documenter sur les transactions, les niveaux d'isolation, les procédures stockées et pdo...