Hoe maak ik een custom auto increment kolom met SQL

Autoincrement moet je als een intern getal zien waar jijzelf niet mee te maken hebt.

Hoe bedoel je? Het is toch mogelijk om een waarde automatisch te laten optellen bij elke insert? Ik weet dat het kan met iets simpels als een id tabel (alleen cijfers) maar hoe dit werkt in combinatie een andere waarde zou ik niet weten.

Ik las iets over een computed kolom waarmee je een waarde kan combineren met auto increment maar kom er niet uit hoe dit te doen.

Dit is een simpel voorbeeld hoe je waarden automatisch invult tijdens insert/update

DROP TABLE IF EXISTS `good_sub`;
CREATE TABLE `good_sub` (
  `num1` bigint(20) unsigned DEFAULT NULL,
  `num2` bigint(20) unsigned DEFAULT NULL,
  `vnum` bigint(20) AS (CAST(num1 AS SIGNED) - CAST(num2 AS SIGNED)) PERSISTENT,
  `vindx` varchar(25) AS ( CONCAT ( num1, ".", num2 )) PERSISTENT,
  UNIQUE KEY `vindx` (`vindx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# De waarden vnum en vindx worden uitgerekend tijdens de insert/update
INSERT INTO `good_sub` (`num1`, `num2`) VALUES
(1001,	12),
(1001,	13);

Adoptive Solution op 03/02/2021 12:51:38

Dit is een simpel voorbeeld hoe je waarden automatisch invult tijdens insert/update

DROP TABLE IF EXISTS `good_sub`;
CREATE TABLE `good_sub` (
  `num1` bigint(20) unsigned DEFAULT NULL,
  `num2` bigint(20) unsigned DEFAULT NULL,
  `vnum` bigint(20) AS (CAST(num1 AS SIGNED) - CAST(num2 AS SIGNED)) PERSISTENT,
  `vindx` varchar(25) AS ( CONCAT ( num1, ".", num2 )) PERSISTENT,
  UNIQUE KEY `vindx` (`vindx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# De waarden vnum en vindx worden uitgerekend tijdens de insert/update
INSERT INTO `good_sub` (`num1`, `num2`) VALUES
(1001,	12),
(1001,	13);

Ik krijg de volgende foutmelding als ik die queries probeer uit te voeren:

MySQL meldt: Documentatie

#1064 - Er is iets fout in de gebruikte syntax bij 'PERSISTENT,
`vindx` varchar(25) AS ( CONCAT ( num1, ".", num2 )) PERSISTENT,
' in regel 4

Probeer het eens met :

 DROP TABLE IF EXISTS `good_sub`;
CREATE TABLE `good_sub` (
  `num1` bigint(20) unsigned DEFAULT NULL,
  `num2` bigint(20) unsigned DEFAULT NULL,
  `vnum` bigint(20) GENERATED ALWAYS AS (cast(`num1` as signed) - cast(`num2` as signed)) STORED,
  `vindx` varchar(25) GENERATED ALWAYS AS (concat(`num1`,'.',`num2`)) STORED,
  UNIQUE KEY `vindx` (`vindx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `good_sub` (`num1`, `num2`) VALUES
(1001,	12),
(1001,	13);

Meer uitleg hier :

https://www.mysqltutorial.org/mysql-generated-columns/


[size=xsmall]Toevoeging op 03/02/2021 13:38:18:[/size]

Nog meer uitleg :

https://dev.mysql.com/doc/refman/8.0/en/alter-table-generated-columns.html

[size=xsmall]Toevoeging op 03/02/2021 13:41:32:[/size]

En nog meer :
https://mariadb.com/kb/en/generated-columns/

Maak je toevallig gebruik van PHPMyAdmin? Zo ja, dan kun je dat daar gewoon eenvoudig mee instellen.

Ozzie PHP op 03/02/2021 13:42:29

Maak je toevallig gebruik van PHPMyAdmin? Zo ja, dan kun je dat daar gewoon eenvoudig mee instellen.

Ja ik gebruik inderdaad phpmyadmin. Hoe kan ik dat daar instellen? Met een trigger oid? Ik zou alleen niet weten wat dan in die trigger te zetten.

[size=xsmall]Toevoeging op 03/02/2021 15:07:12:[/size]

Ik heb geprobeerd deze trigger toe te voegen:

UPDATE orders SET NEW.order_id = CONCAT(NEW.account_id, '.', NEW.ID)

Maar dan krijg ik deze foutmelding wanneer ik een insert probeer:

Can't update table 'orders' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

even terug naar de oorspronkelijke vraag.

Je wilt daar in een "id" kolom zowel het klantnummer als een order-teller opslaan?

Maar ik hoop dat je het klantnummer ook los zult opslaan in een een kolom klant_id of iets dergelijks met een Foreign Key relantie naar de klanten-tabel?

Want als je strakt moet gaan zoeken in de tabel op "de id kolom begint met '1234.'" dan wordt dat in een rap tempo bijzonder traag.

Voor het tonen van ordernummers kan het inderdaad wel leuk zijn dat je klantenservice met 1 blik op het ordernummer weet dat het om klant 33 of 1234 gaat, maar het is niet nodig om dat in je tabel al direct op te nemen.
Dat kun je achteraf combineren, of al dan niet via een trigger of met bovengenoemde tabel-definitie regelen bij het opslaan.

Maar intern kan jouw order best id 329283982 hebben. En als je dat dan toont als 33.1003 omdat in klant-id de waarde 33 staat en je ergens anders bepaald hebt dat 1003 er ook bij hoort, dan zal dat werken.
Maar probeer dan niet om die 33 uit een varchar veld te moeten grabbelen om alle orders te vinden.

Voor de gelegenheid even PHPMyAdmin geïnstalleerd.

Als je een column toevoegt, staat uiterst rechts de optie 'virtualiteit'.
Je kunt daar dan een formule invullen.

Kijk bij een bestaand veld van het eerder gegeven voorbeeld hoe dat eruit ziet.

qua trigger:

BEFORE INSERT
en dan iets als
IF NEW.order_id is NULL
THEN SET NEW.order_id = (NEW.account_id, NEW.ID)
END IF