Discussion :

[devalender]

Bonjour à tous

Je voudrais savoir s'l existe avec Mysql une méthode pour créer une clé auto incrémentée ?
en référence a la base de données Firebird ou, on créer un générateur et ensuite un trigger pour l'incrémenter.
En somme comment créer un clé auto-incrémenté avec Mysql ?

Merci à tous

[tatayo]

Bonjour,
Un peu de recherche ?
En tapant "mysql 5.7 auto increment" dans ton moteur de recherche préféré, les premières lignes te donnent directement un lien vers le résultat.

Tatayo.

[devalender]

Bonjour Tatayo

Oui j'ai fait une recherche, mais j'ai pas eu de réponse à ma question.
En effet dans ma table j'ai en plus de ma clé primaire qui est auto-incrémentée,
je voulais créer une autre clé auto-incrémenté dans le but de sortir une séquence du genre 0001, 0002, 0003 qui ne dépend pas de la clé primaire.
Sous Firebird je sais comment le faire, mais sous MYSQL je ne vois pas trop !

Je vais continuer de chercher !

Merci.

[tatayo]

Il fallait le dire dès le début
L'aide en ligne précise qu'il ne peut y avoir qu'une seule colonne auto-incrémentée.

Peut-être avec une séquence et un trigger ?
Ca devrait fonctionner, mais il n'est pas garanti qu'il n'y aura pas de trou dans la numérotation, ni que l'ordre des insertions soit respectée.

Tatayo.

[devalender]

Après lecture je bute a un premier problème :

Ici je tente de modifier ma table pour y ajouter un champ auto incrémenté :

Code :

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

1
2
 
ALTER TABLE MESCOMMANDES ADD FILIO_COMMANDE INT UNSIGNED NOT NULL AUTO_INCREMENT;

Message d'erreur :

MySQL a répondu : Documentation
#1075 - Un seul champ automatique est permis et il doit être indexé

Donc en faite je comprend que une table on peux pas avoir plus de deux champs de type AUTO_INCREMENT ?

Surement que je ne fais pas correctement les choses ?

Je continu de chercher !

[devalender]

Bonjour à tous

Sur le forum je suis tomber sur ce post : https://www.developpez.net/forums/d4...-cle-primaire/
je vais men inspirer ou tenter une approche avec trigger !

Merci a tous

[isabelle.letrong]

Effectivement, dans MySQL une table ne peut contenir qu'une seule colonne avec l'attribut AUTO_INCREMENT, cette colonne doit nécessairement être définie comme une clé unique, pas nécessairement primaire.
Généralement on se sert de cette colonne comme clé primaire ce qui rend inutilisable cette fonctionnalité par rapport à votre besoin.

La solution vient avec un trigger BEFORE INSERT.
La séquence type de code est :

Code :

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

1
2
3
4
5
6
SELECT 	COALESCE(MAX(MyColonneAutoIncrementToutDeMonCru), 0) + 1
FROM	my_table
WHERE   Restriction                            /* si pas de restriction, MyColonneAutoIncrementToutDeMonCru risque de valoir exactement la valeur de la PK auto-incrémentée automatiquement par mySQL */
INTO   $MyColonneAutoIncrementToutDeMonCru ;   /* Une variable locale du trigger de type 	integer */	

SET    NEW.MyColonneAutoIncrementToutDeMonCru = $MyColonneAutoIncrementToutDeMonCru	;

Alors évidemment reste à savoir pourquoi on aurait besoin d'utiliser plusieurs colonnes auto-incrémentées !!
Personnellement j'utilise cela pour gérer des clés relatives.
On peut typiquement avoir ce besoin pour gérer les lignes d'adresse d'une adresse

Voici l'exemple du DDL + trigger d'une table de lignes d'adresses intégrant une FK vers une table d'adresses (qui elle contient les données non multivaluées de l'adresse comme le code postal, la commune, etc... )

Code :

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

1
2
3
4
5
6
7
CREATE TABLE `addresses_lines` (
  `Address` int(10) unsigned NOT NULL,
  `LineIndex` tinyint(3) unsigned NOT NULL,
  `LineText` varchar(128) NOT NULL,
  PRIMARY KEY (`Address`,`LineIndex`),
  CONSTRAINT `addresses_lines_Address_FK` FOREIGN KEY (`Address`) REFERENCES `addresses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Addresses lines'$$

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
CREATE
DEFINER=`toto`@`%`
TRIGGER `addresses_lines_BINS`
BEFORE INSERT ON `addresses_lines`
FOR EACH ROW
BEGIN
/* ==================================================================================
**	Versions	1.0		XXXX-XX-XX	Initial version
** ==================================================================================
**
**	(D)	Internal datas
*/
DECLARE	$Version	DECIMAL(4,2)	UNSIGNED	DEFAULT	1.0		;
DECLARE	$LineIndex	TINYINT(3)	UNSIGNED				;
/*
**	(T)	Trigger Prolog
*/
SELECT 	COALESCE(MAX(LineIndex), 0) + 1
FROM	addresses_lines
WHERE	Address	=	NEW.Address
INTO	$LineIndex								;
 
SET NEW.LineIndex = $LineIndex							;
 
END
$$

Ne vous posez pas de questions sur les accès concurrents : de tout MySQL s'occupe .....

[devalender]

Bonjour @isabelle.letrong

Je vais parcourir tout ça pas à pas et essayer d'adapter a mon cas.

Merci

[skuatamad]

Ne vous posez pas de questions sur les accès concurrents : de tout MySQL s'occupe .....

Très mauvais conseil en général.
Il faut toujours se poser la question des accès concurrents, et particulièrement dans ce cas.
Dans le cas présent, la session concurrente plante grâce à la clé primaire, mais faire planter l'insertion ne me semble pas être la meilleure approche.

Je préfère passer par une autre table qui stocke la valeur en cours et qui est mise à jour par le trigger.

[isabelle.letrong]

Evidemment je ne parlais pas en général mais bien de ce cas particulier.
La solution proposée est parfaitement opérationnelle, je l'ai mise en place sans soucis chez bien des clients.

Pour revenir à votre proposition skuatamad, il n'y aura pas d'accès concurrents sur votre table de manœuvre qui stocke la valeur en place ?

[skuatamad]

Pour revenir à votre proposition skuatamad, il n'y aura pas d'accès concurrents sur votre table de manœuvre qui stocke la valeur en place ?

Si bien sûr, mais l'update de cette table permet de sérialiser les insertions et évite les erreurs de violation de contraintes.

[isabelle.letrong]

Tout cela est à étudier au regard de l'efficience de la solution. Ce que propose skuatamad impose une gestion de l'intégrité des deux tables (UPDATE table manoeuvre et INSERT table 'principale' ) qu'il faut regarder de près.

En pratique cette colonne auto-incrémentée "à la main" correspond à un besoin fonctionnel.
Dans le cas de clés relatives, telles que les lignes d'adresse, il y a une propriété de localité dans la session (on ne touche qu'aux lignes d'adresse d'une adresse spécifique et il y a fort à parier que les conflits d'accès par deux sessions différentes pour la même adresse soit très très rares), ce qui rend parfaitement suffisante et performante la résolution du conflit par la contrainte de clé primaire..

[skuatamad]

Dans la solution du max + 1, l'ajout d'un LOCK IN SHARE MODE semble résoudre le problème de violation de contrainte, l'attente de la session 2 étant réalisée avant l'exécution du SELECT :

Code :

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

1
2
3
4
SELECT COALESCE(MAX(LineIndex), 0) + 1
FROM addresses_lines
WHERE Address = NEW.Address
INTO $LineIndex lock in share mode;

Tout cela est à étudier au regard de l'efficience de la solution. Ce que propose skuatamad impose une gestion de l'intégrité des deux tables (UPDATE table manoeuvre et INSERT table 'principale' ) qu'il faut regarder de près.

Pas vraiment, enfin si quelqu'un décide de réinitialiser la table de paramétrage, ça posera des problèmes au même titre que si quelqu'un réinitialise une séquence.