Discussion :

[kl4im]

Bonjour,

je souhaite mettre en place une procédure de MAJ d'une base de données, en utilisant "ON DUPLICATE KEY UPDATE", cependant, une erreur de syntaxe est trouvée mais je n'arrive vraiment pas à voir où.
Voici mon code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
 
$query_table_creation = "CREATE TABLE IF NOT EXISTS `protein_sequence` (
		  `acc_number` varchar(10) NOT NULL,
		  `family_number` tinyint(2) DEFAULT NULL,
		  `sec_acc` varchar(250) DEFAULT NULL,
		  `entry_name` varchar(15) DEFAULT NULL,
		  `creation_date` date DEFAULT NULL,
		  `last_update` date DEFAULT NULL,
		  `from_database` varchar(10) NOT NULL,
		  `prot_description` text DEFAULT NULL,
		  `organism` text DEFAULT NULL,
		  `taxonomy` text DEFAULT NULL,
                  `organelle` text DEFAULT NULL,
                  `sequence` text DEFAULT NULL,
                  `seq_length` smallint(3) DEFAULT NULL,
                  `seq_mw` smallint(6) DEFAULT NULL,
                  `phi` double DEFAULT NULL,
                  `keywords` text DEFAULT NULL,
                  `fragment_from` varchar(10) DEFAULT NULL,
                  `fragment_start` tinyint(3) DEFAULT 0,
                  `fragment_end` tinyint(3) DEFAULT 0,
                  `fragment_gap` tinyint(3) DEFAULT 0,
                  `date_insert` date DEFAULT NULL,
                  `date_update` date DEFAULT NULL,
                  `wiki_comment` int(11) DEFAULT NULL,
                  `label_amyloid` int(11) DEFAULT NULL,
		  PRIMARY KEY (`acc_number`))";
 
$create_table = query ($linksql, $query_table_creation);
 
$query_table_update="INSERT INTO `protein_sequence` 
                        (`acc_number`, 
                        `sec_acc`, 
                        `entry_name`, 
                        `creation_date`, 
                        `last_update`, 
                        `from_database`, 
                        `organism`, 
                        `taxonomy`, 
                        `sequence`, 
                        `seq_length`, 
                        `seq_mw`, 
                        `keywords`)
                         VALUES('".$acc_number."', 
                                '".$sec_acc."', 
                                '".$entry_name."', 
                                '".$creation_date."', 
                                '".$updated_date."',
                                '".$dataset."',
                                '".$org_sc.$org_com."',
                                '".$taxonomy."',
                                '".$seq."',
                                '".$seq_length."',
                                '".$seq_mw."',
                                '".$key."')
                         ON DUPLICATE KEY UPDATE   `sec_acc`=VALUES('".$sec_acc."'), 
                             `entry_name`=VALUES('".$entry_name."'), 
                             `creation_date`=VALUES('".$creation_date."'), 
                             `from_database`=VALUES('".$dataset."'), 
                             `organism`=VALUES('".$org_sc.$org_com."'), 
                             `taxonomy`=VALUES('".$taxonomy."'), 
                             `sequence`=VALUES('".$seq."'), 
                             `seq_length`=VALUES('".$seq_length."'), 
                             `seq_mw`=VALUES('".$seq_mw."'), 
                             `keywords`=VALUES('".$key."');";

Trouvez-vous l'erreur ?
Merci d'avance !

[ericd69]

salut,

une primary key en varchar est une horreur à proscrire!!!
y a d'autres trucs qui pourraient être améliorés dans ta table voire base je pense...

sinon si tu nous donnais l'erreur?

à ce que je vois tu utilises mal values() dans l'update... values(nom_colonne) permet d'obtenir la valeur d'une colonne de l'enregistrement que tu cherches à insérer, voir la doc...

[kl4im]

Merci de ta réponse, en effet je ne sais pas pourquoi j'avais mis VALUES, maintenant cela fonctionne.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
ON DUPLICATE KEY UPDATE   `sec_acc`='".$sec_acc."', 
                             `entry_name`='".$entry_name."', 
                             `creation_date`='".$creation_date."', 
                             `from_database`='".$dataset."', 
                             `organism`='".$org_sc.$org_com."', 
                             `taxonomy`='".$taxonomy."', 
                             `sequence`='".$seq."', 
                             `seq_length`='".$seq_length."', 
                             `seq_mw`='".$seq_mw."', 
                             `keywords`='".$key."';";

Pourquoi est-ce qu'une clé primaire en varchar est à proscrire ?

[ericd69]

une clé primaire doit être numérique ou, au pire, une combinaison de colonnes numériques (beaucoup moins performant) car son rôle est spécial:

• identifier de manière unique les lignes donc ça implique l'unicité mais aussi que ses valeurs seront reprisent dans tous les index
• c'est une colonne auto-indexée
• quand tu cherches une valeur dans une colonne texte tu utilises un algorithme (la collation) au lieu d'une comparaison numérique directe c'est donc drastiquement moins efficace. en plus, tu remplis les caches pour index plus vite ce qui les rends moins efficaces...

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
		  `organism` text DEFAULT NULL,
		  `taxonomy` text DEFAULT NULL,
                  `organelle` text DEFAULT NULL,
                  `sequence` text DEFAULT NULL,

tu es sure que ces colonnes auront des valeurs uniques ?
si non elle doivent être remplacées par un identifiant et déportée dans une table annexe (1 par colonne concernées bien sur, les valeurs récupérées par jointure quand c'est nécessaire)
utilise unsigned sur les entiers...
par exemple:

• tinyint unsigned va de 0 à 255
• tinyint va de -128 à +127

t'as vraiment besoin de 2 milliard de valeur dans les 2 colonnes int?
tu peux virer les nombre entre parenthèses dans tes déclarations de colonnes entières ça définit juste les chiffres minimum à afficher pas la taille du contenu...
bref faut compacter tant que faire se peut...

[kl4im]

Je travaille par rapport à Uniprot, une énorme base de données protéiques, et ces protéines sont obligatoirement identifiées de manière unique par une chaîne de caractères de ce style : J9NHJ4. C'est donc pour cette raison que cela avait été choisi comme clé primaire.

Merci pour les astuces, c'est noté. Je reprends le travail de quelqu'un donc pas toujours évident de se rendre compte de tous les petits détails...

[ericd69]

je me doute...

mais tu vois plus la base est grosse plus c'est important...

[kl4im]

En tout cas ce que je voulais fonctionne, maintenant je vais bûcher sur l'optimisation de la base de données, qui en effet est plutôt importante...

[ericd69]

surtout si tu fais des trucs comme du séquençage adn...

y a une autre intervention ou j'ai participé ou on a gagné un facteur 50 environ sur un séquence humain en déterminant la meilleure approche...