Discussion :

[pelloq1]

Bonjour,

Je suis en train d'écrire une fonction d'importation pour mon site web.

Je dois insérer beaucoup d'enregistrements (+ de 3000) dans une base, il s'agit de champs divers le plus gros étant il champ texte pouvant contenir pas mal de texte (l'équivalent d'une page A4).

Ma question est: est il mieux de faire:
- une seule requête avec Insert into ... (col, col...) values (),(),() 3000 fois
- Ou 3000 requêtes
- une boucle avec des sleep php
- une boucle Ajax avec des pauses

Ps: c'est dans une app Joomla.

Cédric

[Artemus24]

Salut pelloq1

Je suis en train d'écrire une fonction d'importation pour mon site web.

Tu veux dire un script php qui va réaliser le chargement de tes tables.

Je dois insérer beaucoup d'enregistrements (+ de 3000) dans une base ...

Tu appelles cela beaucoup +3000 enregistrements ???
Je m'attendais à ce que tu as plus de 10 milliards d'enregistrements. Là oui, cela commence à faire un peu beaucoup.

Ma question est: est il mieux de faire:

1) tu lis en enregistrement depuis ton fichier.
2) tu entres dans la boucle général.
3) pour chaque table à remplir, tu vas faire un seul insert, à partir des champs de ton fichier.
4) pas de pause, pas de sleep, ni quoi que ce soit qui va ralentir le traitement.
5) tant que tu n'as pas atteint la fin de ton fichier, tu reviens au §1.

Si tu as beaucoup d'index dans tes tables, lors du chargement, tu peux les désactiver afin de gagner du temps.
Il ne faudra pas oublier de les rétablir après le chargement.

@+

[pelloq1]

... conclusions hatives....

Non, en fait c'est dans le cadre du développement d'un composant déstiné à être distribué.

Ce composant pour joomla a une fonction d'importation.

L'utilisateur final aura possibilité d'importer des données (pas depuis un fichier mais depuis un textarea... oui un gros).

Donc: c'est une seule table, dont je maitrise les champs:

Code :

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

1
2
3
4
5
6
7
8
9
 
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`ordering` INT(11)  NOT NULL ,
`id_parent` INT NOT NULL ,
`chapitre` VARCHAR(255)  NOT NULL ,
`titre` VARCHAR(255)  NOT NULL ,
`original` TEXT NOT NULL ,
PRIMARY KEY (`id`)
) DEFAULT COLLATE=utf8_general_ci;

Donc en gros tu préconise de faire cela (?):

Code :

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

1
2
3
4
5
6
7
8
9
10
$obj_a_importer = $this->getData();
 
echo count($obj_a_importer); // affiche 3000 ou 4000 ou ... 10000 
 
$query = 'INSERT INTO table (id, ordering, chapitre, titre, original)';
 
foreach($obj_a_importer as $obj)
   $vals [] = '('', $i, $obj->chapitre, $obj->titre, $obj->original)' ; // '', 123, '1.2.3', 'Le titre', 'Le contenu long')
 
$query.= implode(',', $vals);

Donc selon toi de faire une requete de 3 kilomètres de long ne pose pas problème ?

Merci

cedric

[Artemus24]

Salut pelloq1.

1) vous avez fait le choix de l'UTF8.

Vous devez certainement savoir que ce charset utilise pour coder un caractère de 1 à 3 octets.
De ce fait, la longueur de votre chaîne de caractères peut être au maximum le triple en octets.
Cela risque d'avoir un incidence sur la façon de définir vos colonnes dans mysql.

2) comment définir la table MySql ?

Comme vous avez du texte de longueur variable, le type 'char' est à rejeter.
Voici ce qu'indique la documentation sur ces types : http://dev.mysql.com/doc/refman/5.7/...uirements.html

Code :

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

1
2
3
4
5
6
7
VARCHAR    : de 1 à           255 octets ou encore
VARCHAR    : de 1 à         65535 octets.
 
TINYTEXT   : de 1 à           255 octets.
TEXT       : de 1 à        65.535 octets.
MEDIUMTEXT : de 1 à    16.777.215 octets.
LONGTEXT   : de 1 à 4.294.967.295 octets.

Vous avez fait le choix du type 'text'. Avez-vous calculez la longueur maximal en octets et non en caractères de votre texte (ou chaîne de caractères) ?

3) avez-vous pensez à compresser vos table ?

Voici comment déclarer votre table :

Code :

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

1
2
3
4
5
6
7
8
DROP TABLE IF EXISTS `votre_table`;

CREATE TABLE `votre_table`
(
...
) ENGINE=InnoDB
  DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
  ROW_FORMAT=COMPRESSED;

4) et si la colonne 'original' est vraiment très volumineuse ?

Il serait peut-être opportun d'externaliser dans une autre table, juste la colonne 'original'.

Pourquoi ?
D'une part à cause de sa trop grande volumétrie.
Et d'autre part, quand vous faites un accès à votre table, c'est d'abord pour récuprer les chapitres et titres et pas nécessaire son contenu.
Le temps de récupération de vos données risque d'être plutôt long si vous avez la colonne 'original' dans votre table.

Par exemple :

Code :

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

1
2
3
4
5
6
7
CREATE TABLE `original`
( `id` INTEGER UNSIGNED NOT NULL PRIMARY KEY,
  `original` LONGTEXT NOT NULL,
  CONSTRAINT `FK1` FOREIGN KEY (`id`) REFERENCES `votre_table` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
  ROW_FORMAT=COMPRESSED;

Pour faire le lien avec 'votre_table', on utilise une clef étrangère.

5) il faut préparer le texte avant de faire l'insertion !

Ce n'est pas la longueur du texte qui peut poser problème mais tous les caractères qui peuvent parasiter l'insert.
Il faudra convertir certains caractères en utilisant le caractère échappement (escape).

Si dans votre chaîne, vous utilisez l'apostrophe (quote), vous risquez d'avoir un problème d'interprétation.
Par exemple : 'c'est l'été'.
Il y a une apostrophe au début de la chaîne et aussi à la fin.
Pour remédier à cela, il faut remplacer votre chaîne par 'c\'est l\'été' en introduisant le caractère échappement.

En MySql PDO, vous n'avez rien à faire car c'est automatique.
Pour les autres version, il est nécessaire d'utiliser une fonction qui va faire cette substitution.
--> http://php.net/manual/fr/function.my...ape-string.php

6) est-ce la longueur de la chaîne de caractères qui peut poser problème ?

Dans les forums de discussions, le texte est limité à 65.535 octets.
Car, je ne sais pas pourquoi, ils utilisent le type 'text' et non 'longtext'.
Je suppose que c'est un choix qui est dû à un problème d'affichage.

Quand on fait un export d'une base MySql de type forum, il y a des chaines très longue et cela ne pose aucun problème.

En php, une chaîne de caractères peut aller jusqu'à 2 Go.
--> http://php.net/manual/fr/language.types.string.php
donc a priori, cela ne pose aucun problème en php, ni d'ailleurs en MySql.

6) est-ce que la chaîne de caractères peut poser des problème ?

Hormis les caractères particuliers pouvant perturber le insert, le principale problème est dans la façon dont elle va être stockée.

Je vous conseille de vous concentrer sur la Syntaxe Heredoc ainsi que la Syntaxe Nowdoc !
--> http://php.net/manual/fr/language.types.string.php

7) comment faire un insert ?

Je reprends l'exemple avec MySql PDO :

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
<?php
/*------------------------------*/
/* Insertion dans 'votre_table' */
/*------------------------------*/
 
try {
	$ins = $link->prepare("INSERT INTO `votre_table` (`id`, `ordering`, `chapitre`, `titre`, `original`) VALUES (:p1, :p2, :p3, :p4, :p5)");
 
	$ins->bindParam('p1', $id);
	$ins->bindParam('p2', $ordering);
	$ins->bindParam('p3', $chapitre);
	$ins->bindParam('p4', $titre);
	$ins->bindParam('p5', $original);
 
	$ins->execute();
}
catch ( Exception $e ) {
	echo "Une erreur est survenue dans la requête insert : ", $e->getMessage();
	die();
}
?>

8) j'ai fait le test !

Voici la création de la table 'test' :

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
SET AUTOCOMMIT = 0;
START TRANSACTION;
 
-- =========================
-- Base de Données `basephp`
-- =========================
 
DROP DATABASE IF EXISTS `basephp`;
 
CREATE DATABASE `basephp`
	DEFAULT CHARACTER SET `latin1`
	DEFAULT COLLATE       `latin1_general_ci`;
 
USE `basephp`;
 
-- ============
-- Table `test`
-- ============
 
DROP TABLE IF EXISTS `test`;
 
CREATE TABLE `test`
( `id`          INTEGER UNSIGNED NOT NULL PRIMARY KEY,
  `ordering`    INTEGER UNSIGNED NOT NULL,
  `id_parent`   INTEGER UNSIGNED NOT NULL DEFAULT 0,
  `chapitre`    VARCHAR(255)     NOT NULL,
  `titre`       VARCHAR(255)     NOT NULL,
  `original`    LONGTEXT         NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- =================
-- Vidage de `test`
-- =================
 
select * from test;
 
-- ===
-- Fin
-- ===
 
COMMIT;
SET AUTOCOMMIT = 1;

Et voici le bout de code php qui m'a permis de tester l'insertion :

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
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
<!doctype html>
<html>
<head>
<meta charset="ISO-8859-1" />
<title>Chaîne très longue !</title>
<link rel="stylesheet" type="text/css" href="Css/Styles.css">
</head>
 
<body>
<h1>Chargement d'une chaîne très longue !</h1>
<pre>
<?php
require_once("Php/MySql_In.php");
 
$id       = 25;
$ordering = 153;
$titre    = 'Pierre de RONSARD';
$chapitre = 'Mignonne, allons voir si la rose';
$original = <<<EOT
A Cassandre
 
Mignonne, allons voir si la rose
Qui ce matin avoit desclose
Sa robe de pourpre au Soleil,
A point perdu ceste vesprée
Les plis de sa robe pourprée,
Et son teint au vostre pareil.
 
Las ! voyez comme en peu d'espace,
Mignonne, elle a dessus la place
Las ! las ses beautez laissé cheoir !
Ô vrayment marastre Nature,
Puis qu'une telle fleur ne dure
Que du matin jusques au soir !
 
Donc, si vous me croyez, mignonne,
Tandis que vostre âge fleuronne
En sa plus verte nouveauté,
Cueillez, cueillez vostre jeunesse :
Comme à ceste fleur la vieillesse
Fera ternir vostre beauté.
EOT;
 
/*-----------------------*/
/* Insertion dans 'test' */
/*-----------------------*/
 
try {
	$ins = $link->prepare("REPLACE INTO `test` (`id`, `ordering`, `chapitre`, `titre`, `original`) VALUES (:p1, :p2, :p3, :p4, :p5)");
 
	$ins->bindParam('p1', $id);
	$ins->bindParam('p2', $ordering);
	$ins->bindParam('p3', $chapitre);
	$ins->bindParam('p4', $titre);
	$ins->bindParam('p5', $original);
 
	$ins->execute();
}
catch ( Exception $e ) {
	echo "Une erreur est survenue dans la requète insert : ", $e->getMessage();
	die();
}
 
/*----------------------------------------------*/
/* longueur du contenu de la colonne 'original' */
/*----------------------------------------------*/
 
try {
	$sel = $link->query("SELECT length(original) as 'long' FROM `test`;");
	$sel->execute();
}
catch ( Exception $e ) {
	echo "Une erreur est survenue dans la requète select : ", $e->getMessage();
	die();
}
 
/*--------------------------*/
/* affichage de la longueur */
/*--------------------------*/
 
try {
	while ($row = $sel->fetch(PDO::FETCH_ASSOC))
		printf("longueur : %d\n", $row['long']);
}
catch ( Exception $e )
{
	echo "Une erreur est survenue durant l'affichage des résultats : ", $e->getMessage();
	die();
}
 
/*------------------*/
/* Fermeture Cursor */
/*------------------*/
 
$sel->closeCursor();
 
require_once("Php/MySql_Out.php");
?>
</pre>
</body>
</html>

J'ai dupliqué le poème afin d'obtenir une longueur supérieure à plus de 200.000 caractères.

Aucun problème lors du chargement ! J'obtiens bien une longueur très très grande.

@+

[pelloq1]

Bonjour,

Il me faut un peu de temps pour digérer ces informations (structure de la ou des tables si je les scinde).

Bref, cela ne répond pas a ma question, sauf si c'est la phrase qu'on peut avoir jusqu'a 2 GB....

Bref, pour bien me faire comprendre, moi ce que je dois faire c'est:

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
<?php
/*------------------------------*/
/* Insertion dans 'votre_table' */
/*------------------------------*/
 
try {
 
	// oui je sais je peux pas transformer le ins en string, mais c'est juste pour l'exemple
	$ins = "INSERT INTO `votre_table` (`id`, `ordering`, `chapitre`, `titre`, `original`) VALUES ";
 
	for (i=0;i<3000;i++)
		$ins.= "('', $i, '1.2.3', 'Le titre', "L'article");
 
	$ins->execute();
}
catch ( Exception $e ) {
	echo "Une erreur est survenue dans la requête insert : ", $e->getMessage();
	die();
}
?>

Concernant les apostrophes et caractères spéciaux, c'est géré par l'api de joomla, qui a un objet database ->quote($val); .

Cédric

[xounet]

Si la question porte sur la rapidité d'exécution du script, le mieux c'est de créer un fichier "CSV" (chaque champ séparé par des ";" et chaque ligne par un retour à la ligne) avec php par exemple (ça prend quelques dixièmes de secondes pour 3000 lignes). Avec ce fichier tu utilises une requête MYSQL du type :

Code :

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

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

http://dev.mysql.com/doc/refman/5.7/en/load-data.html

L'exécution est très rapide et ne prendra pas 2 secondes avec une machine "basique" je parie. Sans compter la durée de l'upload éventuel...

[pelloq1]

Précisément, ma demande est si ma requête insert comporte 3000 fois l'insertion des valeurs chaînées avec des virgules si mysql va l'accepter ou non.

Code :

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

INSERT INTO table (col, col, col) VALUES (val, val, val),(val, val, val),(val, val, val),(...),...... 3000 fois

Je sais que pour des requêtes avec beaucoup de jointures et de conditions il faut d'abord activer bigquery=1


Merci

[Christophe P.]

Précisément, ma demande est si ma requête insert comporte 3000 fois l'insertion des valeurs chaînées avec des virgules si mysql va l'accepter ou non...

Bonjour,

vous n'avez pas essayé !

Essayez avec 3 000 et si ça passe essayez avec 6 000 pour être tranquille.
Si ça ne passe pas, relisez ce qu'a écrit Artemus24 (très complet et intéressant, merci Artemus24) ou changez votre manière de faire pour utiliser LOAD DATA comme le préconise xounet (je n'ai jamais eu de problème avec et 3 000 lignes ce n'est pas grand chose).

[Artemus24]

Salut pelloq1.

J'ai pris le temps de répondre à ta question en te donnant un maximum de réponses.

A la question concernant la taille maximale d'une variable de type chaîne de caractères en php, elle est de 2 Go.

A la question concernant la taille maximale d'une requête en MySql, je ne sais pas réponse à cette question.
De toute évidence, je n'ai jamais rencontré de problèmes sur l'insert, ni sur le nombre de parenthèses, ni sur la taille total de la requête, ni sur la taille d'une variable à insérer.
Je suis resté raisonnable, et je n'ai jamais créé 3000 parenthèses dans un insert. Au grand maximum, j'ai dû en mettre 20.
Si, 3000 parenthèses est trop grand, rien ne t'empêche de passer à 500 parenthèses, où voire même encore moins.

Question performance, 1000 insert ou 1 seul insert avec 1000 parenthèses, c'est du pareil au même.
Là où cela risque de coincer, c'est l'ordre de chargement de tes lignes (enfin, ce que tu mets dans tes parenthèses).
Il faut trier selon l'ordre de ta primary key, et aussi désactiver certains contrôles que tu peux remettre après le chargement.

Par contre, tu ne soulèves pas la question de la performance de l'accès à ta table.
Je t'ai donné une piste en te disant d'externaliser la colonne "original", qui va considérablement dégrader la performance sur un simple select.

Ensuite, tu ne dis rien sur l'usage que tu veux faire de cette table.
si c'est faire des recherches sur des mots, ton approche est complètement désastreuse.
Il faut utiliser le 'full text serach' : http://dev.mysql.com/doc/refman/5.7/...xt-search.html

Tu n'abordes pas la question de l'organisation de tes données.
Est-ce issue d'un fichier ? Et si oui, de quel type de fichier ? XML, SQL, CSV (fichier excel) ou autre ?

Est-ce que tu as fait des tests ? Le mieux est de commencer par là, non ?
J'ai déjà fait un maximum pour toi.

@+