Dasar Permasalahan
Mysql sudah menyedian auto number dengan auto_increment nya, namun nomor otomatis yang disediakan mysql ini sangat sederhana terkadang tidak sesuai dengan kebutuhan yang kita inginkan.
suatu saat kita membutuhkan nomor otomatis berdasarkan bulan misal
PO-202008-001
PO-202008-002
PO-202008-003
PO-202009-001
PO-202009-002
dst
Dengan fitur yang disediakan mysql tentu tidak akan tergenerate nomor otomatis seperti demikian, oleh karena itu perlu adanya modifikasi dari auto increment pada table mysl.
Solusi.
Buat tabel dengan nama Nomor yang digunakan untuk menanmpung nomor otomatis pada setiap table .
CREATE TABLE IF NOT EXISTS `Nomor` (
`tabel` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
`bulan` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
`created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`tabel`,bulan,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
Tabel Nomor memiliki kombinasi key dari beberapa field yaitu tabel,id,dan bulan. kemudian buat table Orders seperti berikut.
CREATE TABLE `orders` (
`id` char(20) NOT NULL,
`tanggal` date DEFAULT NULL,
`suplier` char(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Kemudian yang perlu kita lakukan adalah membuat trigger pada table orders tersebut untuk set nilai id dari query pada tabel nomor.
DELIMITER $$
DROP TRIGGER /*!50032 IF EXISTS */ `post`.`set_No`$$
create trigger `post`.`set_No` BEFORE INSERT on `post`.`orders`
for each row BEGIN
insert into nomor (tabel,bulan) values('PO',EXTRACT(YEAR_MONTH FROM NEW.TANGGAL));
SET NEW.ID=(SELECT CONCAT(TABEL,'-',EXTRACT(YEAR_MONTH FROM NEW.TANGGAL),'-',LPAD(ID,3,'0'))
FROM NOMOR WHERE TABEL='PO' AND
ID=(SELECT MAX(ID)FROM NOMOR where bulan=EXTRACT(YEAR_MONTH FROM NEW.TANGGAL)) and
bulan=EXTRACT(YEAR_MONTH FROM NEW.TANGGAL) );
END;
$$
DELIMITER ;
Id pada tabel orders merupakan kombinasi dari dengan fungsi penggabungan concat pada mysql yaitu id tabel orders dengan nomor PO dikombinasikan dengan bulan sesuai dengan bulan yang diambil dari tanggal yang diinnpukan dikombinasikan lagi dengan auto increment pada tabel nomor.
Testing .......
INSERT INTO `orders` (`id`, `tanggal`, `suplier`) VALUES ('', '2023-02-01', NULL), ('', '2023-02-01', NULL);
INSERT INTO `orders` (`id`, `tanggal`, `suplier`) VALUES ('', '2023-02-02', 'agus'), ('', '2023-02-02', 'jono');
INSERT INTO `orders` (`id`, `tanggal`, `suplier`) VALUES ('', '2023-03-09', 'baran'), ('', '2023-03-10', 'yono');
hasilnya seperti berikut.
mysql> select*from orders order by tanggal;
+---------------+------------+---------+
| id | tanggal | suplier |
+---------------+------------+---------+
| PO-202302-001 | 2023-02-01 | NULL |
| PO-202302-002 | 2023-02-01 | NULL |
| PO-202302-003 | 2023-02-02 | agus |
| PO-202302-004 | 2023-02-02 | jono |
| PO-202303-001 | 2023-03-09 | baran |
| PO-202303-002 | 2023-03-10 | yono |
+---------------+------------+---------+
6 rows in set (0.00 sec)
Semoga bermanfaat dan menjadi amal jariyah
Salam Query execute
