Fiche PHP : PHP et MYSQL(types données)

Objet de la fiche

État de la fiche

Plan de la fiche :

| Avant propos |
| La base de données MYSQL |
| Structure d'une base de données |
| Types des champs d'une table |
| Récapitulatif des types, attributs et options |
| Optimisation des accés à la base (index) |

Avant propos

Nous allons aborder de façon simplifiée la base de données MYSQL, les commandes SQL permettant d'accéder à ce type de base et l'interraction avec PHP. Seules les commandes les plus usuelles sont abordées dans ce document.

Pour illustrer notre propos nous créront un annuaire avec son outil d'administration pour ajouter, supprimer ou modifier un enregistrement.

La base de données MYSQL

Généralités

Une base de données (BD) est un ensemble structuré et organisé permettant le stockage de grandes quantités d'informations afin d'en faciliter l'exploitation (ajout, mise à jour, effacement et recherche de données).
Ce type de structure est utilisé par des programmes correspondant à des applications distinctes et de manière à faciliter l'évolution indépendante des données et des programmes.

MySQL est l'un de ces Systèmes de Gestion de Bases de Données Relationnelles (SGBDR). C'est une application permettant de gérer des bases de données. Il utilise pour cela le langage SQL.
MySQL peut s'utiliser seul, mais est la plupart du temps combiné à un autre langage de programmation : PHP par exemple pour de nombreux sites web, mais aussi Java, Python, C++, etc.

SQL

SQL signifie "Structured Query Language" soit langage d'interrogation structuré.
C'est un langage complet de gestion de bases de données relationnelles.

Il permet :

d'interroger la BD (commande SELECT).
de manipuler des données (commandes UPDATE, INSERT, DELETE).
de définir des données (commandes CREATE, ALTER).
de controler l'accés aux données (commandes GRANT, REVOKE).

Principe des échanges

L'internaute (1) se connecte (via 2, 3, 4 sur la page WEB hébergée (5).
Les instructions php contenues dans la page WEB nécessite des demandes de données contenues dans le serveur de base de données (6). La demande et la réception des données s'effectuent à l'aide d'une requête SQL. Les donnéees récupérées sont utilisées pour mettre à jour la page WEB qui est envoyée à l'utilisateur.

Structure d'une base de données

Généralités

Une base de données se caractérise par son nom (souvent fourni par le FAI), un login et un mot de passe pour y accéder.

Cette base de donnée va contenir des tables crées par le développeur ou une application.

La structure d’une table implique de lui donner un nom. Dans cette table on va définir des colonnes définies par leurs noms, leurs types, tailles, valeurs par défaut etc en fonction du format des données que l'on veut stocker. Des critères de recherche, et des permissions d'accés particulières pourront aussi être paramétrées.

Pour le nom d'une table les règles suivantes sont à respecter

30 caractères max,
Caractères autorisés: lettres, chiffres, #, $, _ (le premier char doit être une lettre),
Pas de distinction entre minuscules et majuscules (sauf pour les bases de données et tables dans MySQL sous Unix !!),
PAS d’accents,
PAS de mots clefs (SELECT, WHERE, .....).

Structure générale

Types des champs d'une table

Généralités

Dans chaque colonne composant la base on ne peut ranger que des informations de même type. En gros des nombres ou du texte sur lesquels on peut appliquer des critères de recherche.

Types entiers

biginteger	occupe 8 octets et stocke des nombres entiers de -9223372036854775808 à +9223372036854775807 ou des nombres entiers de 0 à +18446744073709551615 si non signé
int	occupe 4 octets et stocke des nombres entiers de -2147483648 à +2147483647 ou des nombres entiers de 0 à +4294967295 si non signé
mediumint	occupe 3 octets et stocke des nombres entiers de -8388608 à +8388607 ou des nombres entiers de 0 à +16777215 si non signé
smallint	occupe 2 octets et stocke des nombres entiers de -32768 à +32767 ou des nombres entiers de 0 à +65535 si non signé
tinyint	occupe 1 octet et stocke des nombres entiers de -128 à +127 ou des nombres entiers de 0 à +255 si non signé

Les types décimaux

decimal	occupe un nombre d'octets variable (fonction du nombre de décimales) et stocke des nombres flottants comme des chaînes de caractères.
float	occupe 4 octets et stocke des nombres flottants à précision simple de -1.175494351E-38 à 3.402823466E+38 ou des nombres flottants à précision simple de 1.175494351E-38 à 3.402823466E+38 si non signé
double	occupe 8 octets et stocke des nombres flottants en double précision de -1.7976931348623157E+308 à -2.2250738585072014E-308, 0, et de 2.2250738585072014E-308 à 1.7976931348623157E+308.
real	occupe 8 octets et stocke des nombres flottants en double précision de manière identique à double

Nota : Utiliser decimal pour stocker des valeurs dont il est primordial de conserver la précision exacte.

Les types boléens et binaires

bit => skocke un champ sous forme de bit (jusqu'à 64).
Un type bit(M) peut stocker un champ de M bits
Exemple : Soit une colonne dont le type est bit(M) et que le nombre de bit de la valeur que l'on veut enregistré soit inférieur au nombre de bit du type défini : dans ce cas les valeurs sont complétées par des 0 à gauche.
Concrètement si on stocke la valeur binaire b'101' dans un champ défini comme BIT(6) en fait c'est b'000101' qui est automatiquement enregistré.
bool => occupe 1 octet et permet de stocker des Vrai/Faux : 1 pour Vrai et 0 pour Faux.

Les types texte et blob

texte

char	accepte de 1 à 255 caractères. La longueur d'un enregistrement de type char est fixée à la longueur que vous avez défini lors de la création de la table. La longueur peut être n'importe quelle valeur entre 1 et 255. Quand une valeur CHAR est enregistrée, elle est complété à droite avec des espaces jusqu'à atteindre la valeur fixée. Quand une valeur de CHAR est lue, les espaces en trop sont retirés.
varchar	accepte de 1 à 255 caractères . Les enregistrements de type varchar sont stockées en utilisant autant de caractères que nécessaire, plus un octet pour mémoriser la longueur. Les valeurs ne sont pas complétées. Au contraire, les espaces finaux sont supprimés avant stockage
tinytext	accepte jusqu'à 255 caractères, et occupe Lg(longueur de la chaine) + 1 octets. Ce champ est insensible à la casse.
text	accepte jusqu'à 65535 caractères, et occupe Lg(longueur de la chaine) + 2 octets. Ce champ est insensible à la casse.
mediumtext	accepte jusqu'à 16777215 caractères, et occupe Lg(longueur de la chaine) + 3 octets.
longtext	accepte jusqu'à 4294967295 caractères, et occupe Lg(longueur de la chaine) + 3 octets.

blob

Les quatre types blob (tinyblob, blob, mediumblob, et longblob) correspondent aux types Texte (tinytext, text, mediumtext, et longtext) équivalents, et ont les mêmes contraintes de stockage. Les seules différences entre les colonnes de type blob et celles de type texte se situent aux niveau des tris et comparaisons : Les tris, faits sur les types blob, contrairement à ceux faits sur les types texte, tiennent compte de la casse. En d'autres termes, une valeur texte est une valeur blob insensible à la casse.

Différences entre char et varchar

Lors de l'enregistrement d'un type char, la valeur est complétée à droite avec des espaces jusqu'à atteindre la valeur fixée. Au contraire pour varchar les valeurs sont stockées en utilisant autant de caractères que nécessaire, plus un octet pour mémoriser la longueur. Les valeurs ne sont pas complétées (les espaces finaux sont supprimés avant stockage).

Valeur	char(4)	Espace requis	varchar(4)	Espace requis
''	' '	4 octets	''	1 octet
'ab'	'ab '	4 octets	'ab'	3 octets
'abcd'	'abcd'	4 octets	'abcd'	5 octets
'abcdefgh'	'abcd'	4 octets	'abcd'	5 octets

Les valeurs lues dans les colonnes de type char(4) et varchar(4) seront les mêmes dans tous les cas, car les espaces finaux sont retirés des valeurs issues de colonnes de type char lors de la lecture.

Les types binaires

Les types binary et varbinary sont similaires à char et varchar, hormis le fait qu'ils contiennent des chaînes binaires, plutôt que des chaînes de texte. C'est à dire, qu'ils contiennent des chaînes d'octets, plutôt que des chaînes de caractères. Cela signifie qu'ils n'ont pas de jeu de caractères associé, et les tris et comparaisons sont basées sur la valeur numérique de l'octet. La taille maximale pour les types binary et varbinary, est la même que celles de char et varchar, hormis le fait que la taille de binary et varbinary est une taille en octets, et non pas en caractères.

Les types dates et heures

Définition

date	occupe 3 octets et stocke une date de '1000-01-01 00:00:00' à '9999-12-31 23:59:59'
datetime	occupe 8 octets. Le type DATETIME est prévu lorsqu'on souhaite stocker une date et une heure. MySQL affiche les valeurs de type DATETIME au format ‘AAAA-MM-JJ HH:MM:SS’.
timestamp	occupe 4 octets et stocke la date et l'heure exprimée en secondes depuis le 1er janviers 1970 : va de '1970-01-01 00:00:00' à quelque part, durant l'année 2037
time	occupe 3 octets et stocke l'heure : va de '-838:59:59' à '838:59:59' La raison de cet intervalle de validité si large est que les colonnes de type TIME peuvent être utilisés pour représenter non seulement des heures du jour, mais aussi des durées entre deux événements (ce qui peut dépasser largement les 24 heures, ou même, être négatif).
year	occupe 1 octet et stocke une annnée qui va de 1901 à 2155 (4 chiffres) ou de 1970 0 2069 (2 chiffres).

Les types constantes

Ces types de champs permettent d'énumérer la liste des valeurs disponibles pour un champ.

ENUM('valeur_possible1','valeur_possible2',...)

Le type ENUM est une liste énumérative. On ne peut stocker dans ce type de colonne qu'une des valeurs déclarées dans la liste.
Occupe 1 ou 2 octets (la place occupée est fonction du nombre de solutions possibles : 65 535 valeurs maximum. Si vous insérez une valeur invalide dans un champs ENUM, la colonne prendra la valeur réservée 0, qui sera représentée par une chaîne vide, en mode chaîne.
Warning : Il est déconseillé de stocker des valeurs numériques dans un ENUM car cela engendre des confusions.

SET('valeur_possible1','valeur_possible2',...)

Le type SET est aussi une liste énumérative, mai au contraire du type ENUM il permet de stocker une ou plusieurs des valeurs déclarées dans la liste (jusqu'à 64).
Occupe 1, 2, 3, 4 ou 8 octets, selon de nombre de solutions possibles (de 0 à 64 valeurs maximum). Si vous insérez une mauvais option dans un ensemble SET, la valeur sera ignorée.
Les valeurs des colonnes SET composées de plusieurs membres sont définies en séparant celles-ci avec des virgules (exemple "un,deux"). Ce qui fait que la valeur d'un membre de SET ne peut contenir lui même de virgule. SET (contrairement à ENUM) autorise le choix de plusieurs valeurs à la fois au sein de la liste.

Warning : A utiliser avec précaution. La portabilité est limitée et une modification de la liste entraine la reconstruction de la table.

Récapitulatif des types, attributs et options.

Légende

uct (*) : pour on update current timestamp.
A I (*) : pour auto incrément.
IC (*) : interclassement.
Par defaut NULL (*) : si la case à cocher NULL autorisé est cochée
Par defaut CT (*) : pour current timestamp
(M) : défini le nombre de caractères maximum que le champ pourra contenir.
(M,D) : Pour un nombre décimal M défini le nombre de chiffres significatifs, D défini le nombre de chiffre aprés le point.

Groupes	Types	Attributs				Par Defauts		A I (*)	IC (*)
		binary	unsigned	zerofill	uct (*)	NULL (*)	CT (*)
Numériques	biginteger(M)	Non	Oui	Oui	Non	Oui	Non	Oui	Non
	int(M)	Non	Oui	Oui	Non	Oui	Non	Oui	Non
	mediumint(M)	Non	Oui	Oui	Non	Oui	Non	Oui	Non
	smallint(M)	Non	Oui	Oui	Non	Oui	Non	Oui	Non
	tinyint(M)	Non	Oui	Oui	Non	Oui	Non	Oui	Non

	decimal(M,D)	Non	Oui	Oui	Non	Oui	Non	Oui	Non
	float(M,D)	Non	Oui	Oui	Non	Oui	Non	Oui	Non
	real(M,D)	Non	Oui	Oui	Non	Oui	Non	Oui	Non
Booléen Binaire	bit	Oui	Non	Non	Non	Oui	Non	Non	Non
Booléen Binaire	bool	Non	Non	Non	Non	Non	Non	Non	Non
Texte	char(M)	Oui	Non	Non	Non	Non	Non	Non	oui
	varchar(M)	Oui	Non	Non	Non	Non	Non	Non	oui
	tinytext	Oui	Non	Non	Non	Non	Non	Non	oui
	text	Oui	Non	Non	Non	Non	Non	Non	oui
	mediumtext	Oui	Non	Non	Non	Non	Non	Non	oui
	longtext	Oui	Non	Non	Non	Non	Non	Non	oui
	tinyblob	Oui	Non	Non	Non	Non	Non	Non	oui
	blob	Oui	Non	Non	Non	Non	Non	Non	oui
	mediumblob	Oui	Non	Non	Non	Non	Non	Non	oui
	longtblob	Oui	Non	Non	Non	Non	Non	Non	oui
Chaines binaires	binary(M)	Non	Non	Non	Non	Non	Non	Non	Non
Chaines binaires	varbinary(M)	Non	Non	Non	Non	Non	Non	Non	Non
Dates	date	Non	Non	Non	Non	Non	Non	Non	Non
	timestamp(M)	Non	Non	Non	Oui	Non	Non	Non	Non
	time	Non	Non	Non	Non	Non	Non	Non	Non
	year	Non	Non	Non	Non	Non	Non	Non	Non
Constantes	enum	Oui	Non	Non	Non	Non	Non	Non	Oui
	set	Oui	Non	Non	Non	Non	Non	Non	Oui

Options

Option de spécification de la taille d'affichage

pour les numériques

Cette option ne concerne que la forme de présentation des données et n'influence pas les limites de stockage, ou la valeur exacte de l'information.
Syntaxe : int(M) , smallint(M) ou tinyint(M) : M est le nombre maximal de chiffres : la précision. Il prend une valeur entière entre 1 et 64.
Exemple : int(4)
Nota : Une valeur supérieure au format sera enregistré normalement.

pour les décimaux

Cette option concerne la taille de stockage nécessaire pour la valeur.
Syntaxe : decimal(M,D) , float(M,D) ou double(M,D) : M est le nombre maximal de chiffres : la précision, D est le nombre de chiffres décimaux : l'échelle. Il peut prendre des valeurs de 1 à 30, et ne doit pas dépasser M.
Exemple DECIMAL(5,2)
Nota : Une valeur supérieure au format sera enregistrée à la valeur maximale permise par le format M,D.

exemples

exemple int(4)
Valeurs	Stockage	Longueur
2	---2	4
21	--21	4
1250	1250	4
14250	14250	5
350000	350000	6

decimal(5,2)
Valeurs	Stockage
1.3	1.30
3.678	3.68
30.25	30.25
110.35	110.35
2785.28	999.99

Options par défaut

Les colonnes blob et texte ne peuvent avoir de valeur par défaut.

null

On peut sélectionner une valeur par défaut à un champ lorsque rien n'est spécifié. Par défaut, celle-ci est vide mais on peut lui attribuer la valeur « NULL » (pas de valeur).

Un enregistrement vide dans une colonne avec l'attribut NULL contiendra automatiquement NULL qui signifie "vide", et PAS zéro ou "" !!.

Une valeur nulle ou inexistante enregistrée dans une colonne avec l'attribut NOT NULL génèrera une erreur lors de l'enregistrement.

current timestamp

Cette option permet de dater automatiquement l'entrée au moment de sa création.

Attributs

binary

Les valeurs dans les colonnes sont classées et comparées sans tenir compte de la casse, à moins que l'attribut binary n'ai été spécifié lors de la création de la colonne : les valeurs sont alors classées et triées en tenant compte de la casse.

unsigned

Les valeurs non-signées peuvent être utilisées pour n'autoriser que des valeurs positives dans une colonne, ou bien pour exploiter un intervalle de validité plus haut.

Attention : Lorsque l'on doit stocker une valeur qui est hors de l'intervalle de validité d'une colonne, il ramène la valeur à la plus proche possible, et stocke cette valeur. Par exemple, l'intervalle de validité d'une colonne d'entiers INT va de -2147483648 à 2147483647 . Si vous essayez d'insérer -9999999999 dans une colonne de ce type, la valeur sera ramenée à la plus proche possible, c'est à dire -2147483648 . De même, si vous essayez d'insérer 9999999999 , 2147483647 sera stocké à la place.

zerofill

ZEROFILL justifie les nombres à gauche avec des 0 : exemple dans une colonne dont le type est int(4), la valeur 40 sera affichée 0040

ZEROFILL agit sur l'affichage mais n'a aucun effet sur le mode de stockage de l'information.

Attention : l'usage de ZEROFILL implique automatiquement l'attribut UNSIGNED.

on update current timestamp

Cette option permet de dater automatiquement l'entrée au moment de sa mise à jour.

Extra

auto increment

une seule colonne avec auto incrément

interclassement

Un interclassement (ou collation en Anglais) est un ensemble de règles permettant la comparaison de caractères dans un jeu. Ce paramètre influe donc sur le résultat des tris. MySQL vous propose plusieurs interclassements pour l'UTF-8. La plupart sont spécifiques à certaines langues, comme utf8_esperanto_ci qui est prévu pour l'Esperanto. Si vous ne travaillez pas dans l'une de ces langues, comme c'est le cas pour le Français, il vous reste 3 autres options :

utf8_bin. Cet interclassement compare les codes des caractères. Il est donc très rapide, mais pas forcément très précis. Comme les codes des majuscules sont plus petits que ceux des minuscules (A = 65, a = 97), les enregistrements comportant du texte en majuscules se retrouveront placés avant les textes écrit en minuscules, ce qui ne correspondra pas forcément aux attentes de l'utilisateur.
utf8_general_ci. Cet interclassement fonctionne de façon très simple. Il supprime les accents et converti le texte en minuscules. Il est donc assez rapide.
utf8_unicode_ci est plus précis car il supporte les caractères multiples comme le e dans l'o. En contrepartie, il peut s'avérer un peu plus lent.

Donc à priori, pour le Français, on peut utiliser utf8_bin sur une colonne où tout le texte est soit en minuscules, soit en majuscules de façon à obtenir les meilleures performances. Si on n'est pas sur de la casse des caractères, il faut utiliser utf8_general_ci. Enfin, si vous travaillez avec plusieurs langues différentes, utf8_unicode_ci sera alors peut-être plus adapté.

Optimisation des accés à la base (index)

Clés	Commentaires
Primaire	Une clé primaire doit être unique. Elle représente une colonne ou un groupe de colonnes qui sont le plus souvent utilisées lors des recherches SQL. Déclarer une colonne comme clé primaire a pour effet d'y interdire les Null et les doublons. Si la table est trés importante, mais sans nécessité absolue d'utiliser une clé primaire, on peut utiliser une colonne dédiée avec une valeur auto-increment et l'utiliser comme une clé primaire servant de pointeurs sur les différentes lignes de la table.
Unique	Un index UNIQUE impose que toutes les valeurs liées à cet index soient distinctes. Une erreur est générée si l'on tente d'ajouter une ligne avec un champ (indexée UNIQUE) dont une même valeur existe déjà dans la base. Attention pour certains moteur SQL, un index UNIQUE permet toutefois plusieurs valeurs de type NULL.
Index	Les indexes améliorent la performance des opérations SELECT. Chaque table peut avoir 16 indexes (max.). On peut indexer toutes les colonnes (SAUF blob et text), mais les colonnes doivent être déclarées non NULL. On peut limiter l’indexage des CHAR et VARCHAR à quelques caractères. Transposé au monde informatique cela donne un serveur MySQL qui compare une à une les entrées du botin pour trouver toutes celles qui correspondent au nom recherché. Si au contraire ce nom est indexé, et si celui-ci commence par exemple par ‘T’, le serveur sait directement qu’il doit démarrer sa recherche à partir du ‘T’ et la finir au début du 'U'.
Full-text	Full-text consiste en un mode de recherche dite textuelle, où il est possible de rechercher un mot ou une phrase dans une base de données, sur plusieurs champs en effectuant un tri du mot recherché. Ce filtrage diffère de l’opérateur LIKE qui ne retrouve seulement que la présence de la chaîne sur un champs.