Kode Urut Otomatis dengan MySQL

Terkadang terdapat suatu kondisi yang membutuhkan pengurutan kode secara manual dalam database MySQL, walaupun sudah terdapat pengkodean urut secara sistem (autoincrement).

Artikel ini menjelaskan cara pembuatan kode urut melalui Query SQL seperti contoh format di bawah ini :

• 001, 002, 003, … 999
• A0001, A0002, … A9999
• AB00001, AB00002, … AB99999

Ketentuan penggunaan Query SQL adalah sebagai berikut :

1. Mesti ada data awal dalam suatu tabel sebagai format contoh, misalkan : A001. Selanjutnya Query SQL baru dapat menghasilkan kode selanjutnya sesuai dengan format pengurutan
2. Kode awal dapat hanya berupa angka, atau mengandung alphabet di depan (maksimal 3 huruf)
3. Jumlah maksimum digit mengikuti data format awal, sebagai contoh A001 mempunyai kode maksimum A999

Query SQL :

SELECT IFNULL(CONCAT(kode,LPAD(angka,lebar_angka,'0')),'') AS kode FROM
  (
  SELECT CONCAT(
    CASE SUBSTR(nama_field_kode,1,1) REGEXP '[A-Z]' WHEN 1 THEN SUBSTR(nama_field_kode,1,1) ELSE '' END,
    CASE SUBSTR(nama_field_kode,2,1) REGEXP '[A-Z]' WHEN 1 THEN SUBSTR(nama_field_kode,2,1) ELSE '' END,
    CASE SUBSTR(nama_field_kode,3,1) REGEXP '[A-Z]' WHEN 1 THEN SUBSTR(nama_field_kode,3,1) ELSE '' END
  ) AS kode,
  CONCAT(
    CASE SUBSTR(nama_field_kode,3,1) REGEXP '[A-Z]' WHEN 1 THEN LENGTH(SUBSTR(nama_field_kode,4)) ELSE
    CASE SUBSTR(nama_field_kode,2,1) REGEXP '[A-Z]' WHEN 1 THEN LENGTH(SUBSTR(nama_field_kode,3)) ELSE
    CASE SUBSTR(nama_field_kode,1,1) REGEXP '[A-Z]' WHEN 1 THEN LENGTH(SUBSTR(nama_field_kode,2)) ELSE
    LENGTH(nama_field_kode)
      END
    END END
  ) AS lebar_angka,
  CONCAT(
    CASE SUBSTR(nama_field_kode,3,1) REGEXP '[A-Z]' WHEN 1 THEN MAX(SUBSTR(nama_field_kode,4))+1 ELSE
    CASE SUBSTR(nama_field_kode,2,1) REGEXP '[A-Z]' WHEN 1 THEN MAX(SUBSTR(nama_field_kode,3))+1 ELSE
    CASE SUBSTR(nama_field_kode,1,1) REGEXP '[A-Z]' WHEN 1 THEN MAX(SUBSTR(nama_field_kode,2))+1 ELSE
    MAX(nama_field_kode)+1
      END
    END END
  ) AS angka
  FROM nama_tabel GROUP BY 1 LIMIT 1
) temp;

Penjelasan Sistem Kerja Query SQL :

1. Pencarian kode inisial (jika ada), misalkan A pada A001
2. Penghitungan jumlah maksimum digit
3. Nilai urut sesudah angka terbesar
4. Penggabungan kode inisial(poin 1), dengan nilai urut selanjutnya(poin 3) dengan lebar digit sesuai dengan poin 2

Penjelasan parameter :

• nama_tabel : nama tabel
• nama_field_kode : field yang menyimpan kode urut (varchar)

Jika ingin menjadikannya sebagai Store Procedure :

CREATE PROCEDURE sp_getKodeBaru(IN nama_tabel VARCHAR(255),
  IN nama_field_kode VARCHAR(255),
  OUT varhasil VARCHAR(255))
BEGIN
  SET @strQuery =
  CONCAT('SELECT IFNULL(CONCAT(kode,LPAD(angka,lebar_angka,''0'')),'''') INTO @varhasil FROM ',
    '(
    SELECT CONCAT(
      CASE SUBSTR(',nama_field_kode,',1,1) REGEXP ''[A-Z]'' WHEN 1 THEN SUBSTR(',nama_field_kode,',1,1) ELSE '''' END,
      CASE SUBSTR(',nama_field_kode,',2,1) REGEXP ''[A-Z]'' WHEN 1 THEN SUBSTR(',nama_field_kode,',2,1) ELSE '''' END,
      CASE SUBSTR(',nama_field_kode,',3,1) REGEXP ''[A-Z]'' WHEN 1 THEN SUBSTR(',nama_field_kode,',3,1) ELSE '''' END
    ) AS kode,
    CONCAT(
      CASE SUBSTR(',nama_field_kode,',3,1) REGEXP ''[A-Z]'' WHEN 1 THEN LENGTH(SUBSTR(',nama_field_kode,',4)) ELSE
      CASE SUBSTR(',nama_field_kode,',2,1) REGEXP ''[A-Z]'' WHEN 1 THEN LENGTH(SUBSTR(',nama_field_kode,',3)) ELSE
      CASE SUBSTR(',nama_field_kode,',1,1) REGEXP ''[A-Z]'' WHEN 1 THEN LENGTH(SUBSTR(',nama_field_kode,',2)) ELSE
      LENGTH(',nama_field_kode,')
        END
      END END
    ) AS lebar_angka,
    CONCAT(
      CASE SUBSTR(',nama_field_kode,',3,1) REGEXP ''[A-Z]'' WHEN 1 THEN MAX(SUBSTR(',nama_field_kode,',4))+1 ELSE
      CASE SUBSTR(',nama_field_kode,',2,1) REGEXP ''[A-Z]'' WHEN 1 THEN MAX(SUBSTR(',nama_field_kode,',3))+1 ELSE
      CASE SUBSTR(',nama_field_kode,',1,1) REGEXP ''[A-Z]'' WHEN 1 THEN MAX(SUBSTR(',nama_field_kode,',2))+1 ELSE
      MAX(',nama_field_kode,')+1
        END
      END END
    ) AS angka
    FROM ',nama_tabel,' GROUP BY 1 LIMIT 1
    ) tabel_temp');
  PREPARE stmt FROM @strQuery;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

  SET varhasil = @varhasil;
END

Penjelasan parameter :

• nama_tabel : nama tabel
• nama_field_kode : field yang menyimpan kode urut (varchar)
• varhasil : penampung hasil (kode urut selanjutnya)

Cara pemanggilan Store Procedure di atas :

CALL sp_getKodeBaru('tabel1','kode_urut',@varhasil);
SELECT @varhasil AS kode;

Bayu Kandukeswara