(1). NULL et NOT NULL

NOT NULL empêche l'insertion de valeurs NULL, mais autorise les chaînes vides.

Les types numériques, les chaînes de caractères et les types de date peuvent accepter NULL, mais seules les chaînes de caractères peuvent contenir des valeurs vides.

Exemple d'utilisation :

mysql> create database bd_test;
Query OK, 1 row affected (0.00 sec)

mysql> use bd_test;
Database changed
mysql> create table `table_sans_null`(prenom varchar(20) not null);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into table_sans_null values (null);　　// La colonne avec not null n'autorise pas NULL
ERROR 1048 (23000): Column 'prenom' cannot be null
mysql> insert into table_sans_null values ('');　　// Mais autorise les chaînes vides
Query OK, 1 row affected (0.00 sec)

mysql> insert into table_sans_null values ('Sophie'); 
Query OK, 1 row affected (0.01 sec)

Différence entre valeur vide et NULL : la valeur vide n'occupe pas d'espace, tandis que NULL occupe 1 octet.

NOT NULL est plus efficace que NULL car NULL n'est pas une valeur vide, occupe de l'espace, et participe aux comparaisons de champs, ce qui affecte la performance. De plus, les index ne stockent pas les valeurs NULL, ce qui réduit l'efficacité des index.

(2). DEFAULT

DEFAULT permet de définir une valeur par défaut.

mysql> create table table_defaut(id int not null,utilisateur varchar(20) not null default 'invite');
Query OK, 0 rows affected (0.03 sec)

mysql> insert into table_defaut(id) values(5);
Query OK, 1 row affected (0.00 sec)

mysql> select * from table_defaut;
+----+-----------+
| id | utilisateur|
+----+-----------+
|  5 | invite    |
+----+-----------+
1 row in set (0.00 sec)

Remarque : pour un champ de type chaîne sans DEFAULT, si NULL est autorisé, la valeur par défaut est NULL ; si NULL n'est pas autorisé, une erreur est générée. Les types ENUM sont traités séparément, avec la première valeur comme défaut. Les champs de date n'ont pas de DEFAULT, prenant par défaut la date actuelle.

(3). AUTO_INCREMENT

AUTO_INCREMENT incrémente automatiquement la valeur de 1 à chaque insertion, et ne peut être utilisé qu'avec les types numériques. Cependant, l'utilisation d'AUTO_INCREMENT nécessite généralement que le champ soit une clé primaire, sinon MySQL renverra une erreur 1075.

mysql> create table table_auto(id bigint auto_increment primary key,nom varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into table_auto(nom) values('produit');
Query OK, 1 row affected (0.00 sec)

mysql> select * from table_auto;
+----+---------+
| id | nom     |
+----+---------+
|  1 | produit |
+----+---------+
1 row in set (0.00 sec)

(4). Extension : Effacer les données d'une table, y compris la valeur AUTO_INCREMENT

Généralement, la commende DELETE est utilisée pour supprimer des données, mais DELETE ne peut pas réinitialiser la valeur AUTO_INCREMENT. Par exemple :

mysql> select * from table_auto;
+----+---------+
| id | nom     |
+----+---------+
|  1 | produit |
+----+---------+
1 row in set (0.00 sec)

mysql> delete from table_auto;
Query OK, 1 row affected (0.03 sec)

mysql> insert into table_auto(nom) values('produit2');
Query OK, 1 row affected (0.01 sec)

mysql> select * from table_auto;
+----+----------+
| id | nom      |
+----+----------+
|  2 | produit2 |
+----+----------+
1 row in set (0.00 sec)

Dans ce cas, il faut utiliser la commande TRUNCATE. Voici comment :

mysql> select * from table_auto;
+----+----------+
| id | nom      |
+----+----------+
|  2 | produit2 |
+----+----------+
1 row in set (0.00 sec)

mysql> truncate table table_auto;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into table_auto(nom) values('produit3');
Query OK, 1 row affected (0.01 sec)

mysql> select * from table_auto; 
+----+----------+
| id | nom      |
+----+----------+
|  1 | produit3 |
+----+----------+
1 row in set (0.00 sec)

Attention : TRUNCATE supprime toutes les données de la table. Si vous souhaitez uniquement réinitialiser la valeur AUTO_INCREMENT, n'utilisez pas cette commande.

(5). Extensions

Outre les contraintes mentionnées ci-dessus, il existe également les contraintes de vérification (CHECK), les contraintes de clé étrangère (FOREIGN KEY), les contraintes de clé primaire (PRIMARY KEY) et les contraintes d'unicité (UNIQUE). Les contraintes de clé primaire et d'unicité sont également des index.