De PHPhulp kroeg
Hoe maak ik een custom auto increment kolom met SQL
Bijvoorbeeld: 33.1000
33 is het id van de gebruiker, en 1000 is het aantal orders (totaal, dus 33.1000 kan worden opgevold door bijvoorbeeld 50.1001, 62.1002 etc).
Het account id sla ik los op in de order tabel maar ik zou niet weten hoe ik die plak aan een order id kolom met daarnaast een auto increment na de punt die begint bij 1000.
Hoe doe ik zoiets?
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.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
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);
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
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
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);
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
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
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);
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/
Toevoeging op 03/02/2021 13:38:18:
Nog meer uitleg :
https://dev.mysql.com/doc/refman/8.0/en/alter-table-generated-columns.html
Toevoeging op 03/02/2021 13:41:32:
En nog meer :
https://mariadb.com/kb/en/generated-columns/
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.
Toevoeging op 03/02/2021 15:07:12:
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.
Gewijzigd op 03/02/2021 14:47:59 door Snelle Jaap
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.
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.
BEFORE INSERT
en dan iets als
IF NEW.order_id is NULL
THEN SET NEW.order_id = (NEW.account_id, NEW.ID)
END IF
Wat als iemand bedenkt dat je in plaats van 33.1000 toe liever 33-1000 op je factuur ziet?
Wat als een order om een of andere reden omgezet wordt van klantnummer 88 naar klantnummer 33 omdat hij er achter kwam dat er al een account was van hem?
Je kunt trouwens ook een VIEW overwegen voor je (afgeleide) data.
Snelle Jaap op 03/02/2021 14:32:33:
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.
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.
Ik heb wellicht te snel gelezen. Ik dacht dat je vraag was hoe je een autoincrement op een kolom moet instellen.
>> 33 is het id van de gebruiker, en 1000 is het aantal orders
Als ik je goed begrijp wil je in 1 en dezelfde kolom het id van de gebruiker opslaan en het totaal aantal van alle aanwezige orders?
WAt hebben die 2 met elkaar te maken, en waarom zou je die in 1 kolom opslaan? Graag wat meer uitleg.
Ik weet dat het kan maar de vraag was dus even hoe.
Code (php)
1
2
2
SELECT id, klant_id, aantal, what, ever, CONCAT(klant_id, '.', getal) AS geformateerdnummer
FROM tabel
FROM tabel
Maar dan zou je ook in een view kunnen doen.
Ik heb het idee dat de waarde 1000 ook niet ergens vastligt, maar nog berekend moet worden.
(iets als "aantal regels in een zekere tabel" + 1000 ?)
Ik denk dat je nu iets wil gaan doen wat niet echt handig is.
Stel ik ben klant 1 en jij bent klant 500. We plaatsen allebei een order. Eerst jij, dan ik. De ordernummers worden nu:
500.1000
1.1001
Ik heb mijn order later geplaatst, maar mijn ordernummer is kleiner.
Wat jij wil lijkt me niet handig.
Doe je dit in eigen beheer of in opdracht van iemand?
Dat mag jij denken, maar denk je ook dat de Belastingdienst het geen probleem vindt als jouw ordernummers niet op normale wijze oplopen? Je combineert 2 dingen met elkaar die niks met elkaar te maken hebben.
Dat mag hè ... het is jouw systeem. Alleen wijs ik je er nu alvast op dat het niet verstandig is. Of jij daar iets mee doet, is aan jou.