KAL
Menu

ALTER TABLE

Mengubah skema secara aman, termasuk kesadaran online DDL pada tabel besar.

Daftar isi

Skema yang baik tetap akan berubah, dan ALTER TABLE adalah pernyataan yang mengubah struktur tabel yang sudah berisi data. Menambah kolom, memperlebar tipe, mengganti nama, dan menghapus kolom semuanya dikerjakan server tanpa membuang data yang ada. Topik ini menjalankan rangkaian ALTER TABLE pada tabel berisi tiga ribu baris, membaca definisi sebelum dan sesudahnya, serta mengenalkan online DDL, yaitu mekanisme InnoDB yang membuat perubahan skema tetap aman untuk tabel yang terus dilayani.

Persiapan tabel berisi tiga ribu baris#

Perubahan struktur hanya terasa berbeda dari pembuatan tabel apabila tabelnya sudah terisi, sehingga latihan ini dimulai dari tabel anggota yang diisi tiga ribu baris. CTE rekursif menghasilkan deret nomor dalam satu pernyataan, dan variabel cte_max_recursion_depth dinaikkan terlebih dahulu karena bawaannya hanya seribu langkah.

sql skema-perpustakaan.sql 
-- tidak-dijalankan

DROP DATABASE IF EXISTS perpustakaan;
CREATE DATABASE perpustakaan;
USE perpustakaan;

CREATE TABLE anggota (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  nama VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_email (email)
) ENGINE=InnoDB;
bash 
# membuat ulang basis data perpustakaan dari nol
mysql -u root < skema-perpustakaan.sql
bash 
# mengisi tiga ribu baris anggota menggunakan CTE rekursif
mysql -t -u root perpustakaan <<'SQL'
SET SESSION cte_max_recursion_depth = 1000000;
INSERT INTO anggota (nama, email)
WITH RECURSIVE deret (n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM deret WHERE n < 3000
)
SELECT CONCAT('Anggota ', n), CONCAT('anggota', n, '@mail.test')
FROM deret;
SELECT COUNT(*) AS jumlah_baris FROM anggota;
SQL
text Keluaran 
+--------------+
| jumlah_baris |
+--------------+
|         3000 |
+--------------+

Definisi tabel direkam terlebih dahulu sebagai pembanding, sebelum satu pun perubahan dilakukan.

bash 
# menampilkan definisi kanonis tabel sebelum perubahan
mysql -t -u root perpustakaan -e "SHOW CREATE TABLE anggota\G"
text Keluaran 
*************************** 1. row ***************************
       Table: anggota
Create Table: CREATE TABLE `anggota` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `nama` varchar(100) NOT NULL,
  `email` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=4096 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Klausa AUTO_INCREMENT=4096, bukan 3001, adalah akibat samping yang wajar dari penyisipan massal. Penyisipan melalui INSERT ... SELECT memaksa InnoDB mengalokasikan nomor dalam kelompok yang besarnya dibulatkan pangkat dua, sehingga counter melewati jumlah barisnya.

Menambah kolom dengan online DDL#

Penambahan kolom adalah operasi ALTER TABLE yang paling sering dilakukan. Pernyataan berikut sekaligus menyebut ALGORITHM=INPLACE dan LOCK=NONE untuk menuntut metode yang paling ramah tabel hidup, yaitu perubahan di dalam InnoDB tanpa menahan penulisan data.

bash 
# menambah kolom status dengan metode online
mysql -u root perpustakaan -e "ALTER TABLE anggota ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'aktif', ALGORITHM=INPLACE, LOCK=NONE"

Perintah di atas berjalan tanpa keluaran karena seluruh DDL pada mode batch diam ketika berhasil. Setiap baris yang ada kini memuat nilai bawaan aktif, dan baris baru tanpa nilai status juga akan mengisinya secara otomatis.

bash 
# memeriksa isi tiga baris teratas setelah penambahan kolom
mysql -t -u root perpustakaan -e "SELECT id, nama, email, status FROM anggota ORDER BY id LIMIT 3"
text Keluaran 
+----+-----------+--------------------+--------+
| id | nama      | email              | status |
+----+-----------+--------------------+--------+
|  1 | Anggota 1 | [email protected] | aktif  |
|  2 | Anggota 2 | [email protected] | aktif  |
|  3 | Anggota 3 | [email protected] | aktif  |
+----+-----------+--------------------+--------+

Mengubah nama dan tipe kolom#

Satu pernyataan ALTER TABLE boleh memuat beberapa operasi sekaligus, dan semuanya dikerjakan dalam satu proses pembangunan ulang. Pernyataan berikut mengganti nama kolom status menjadi keanggotaan, menambah kolom catatan, dan memperlebar nama menjadi VARCHAR(150).

bash 
# menggabungkan tiga perubahan struktur dalam satu pernyataan
mysql -u root perpustakaan -e "ALTER TABLE anggota RENAME COLUMN status TO keanggotaan, ADD COLUMN catatan VARCHAR(255) NULL, MODIFY nama VARCHAR(150) NOT NULL"

Definisi kanonis setelah perubahan memperlihatkan ketiganya terpasang, sedangkan data lama tetap utuh.

bash 
# menampilkan definisi kanonis tabel setelah perubahan
mysql -t -u root perpustakaan -e "SHOW CREATE TABLE anggota\G"
text Keluaran 
*************************** 1. row ***************************
       Table: anggota
Create Table: CREATE TABLE `anggota` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `nama` varchar(150) NOT NULL,
  `email` varchar(150) NOT NULL,
  `keanggotaan` varchar(20) NOT NULL DEFAULT 'aktif',
  `catatan` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=4096 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Perhatikan bahwa penggantian nama melalui RENAME COLUMN tidak menyentuh data sama sekali. Bentuk lama CHANGE COLUMN masih diterima tetapi mengharuskan definisi kolom ditulis ulang, sehingga RENAME COLUMN menjadi pilihan yang lebih aman dari kesalahan ketik.

Menjaga aman dengan ALGORITHM#

InnoDB menjalankan perubahan skema melalui tiga algoritme, dari yang paling murah ke paling mahal. INSTANT hanya menyentuh metadata dan selesai dalam sekejap, INPLACE membangun ulang tabel di dalam InnoDB sambil tetap melayani pembacaan dan penulisan, sedangkan COPY menyalin seluruh tabel ke tabel sementara dan menahan penulisan selama prosesnya. Bentuk penuh online DDL ditulis seperti berikut.

Klausul LOCK melengkapi pilihan algoritme dengan tingkat akses yang dipertahankan selama perubahan. LOCK=NONE mempertahankan pembacaan dan penulisan, LOCK=SHARED masih mengizinkan pembacaan saja, sedangkan LOCK=EXCLUSIVE menutup tabel dari koneksi lain. Server menolak permintaan yang tidak didukung operasinya, sehingga kedua klausul ini sebaiknya selalu dituliskan bersama.

sql Bentuk penuh online DDL 
-- tidak-dijalankan
ALTER TABLE nama_tabel
  ADD COLUMN kolom_baru INT NOT NULL DEFAULT 0,
  ALGORITHM=INPLACE, LOCK=NONE;

Tanpa klausul ALGORITHM, server bebas memilih metodenya sendiri, dan perubahan yang terasa ringan dapat terjadi dengan cara yang mahal. Klausul ini menjadi rem pengaman, karena server menolak langsung apabila algoritme yang diminta tidak didukung operasinya, alih-alih diam-diam memakai cara yang lambat.

bash 
# menuntut algoritme INSTANT untuk perubahan tipe yang butuh pembangunan ulang
mysql -u root perpustakaan -e "ALTER TABLE anggota MODIFY nama TEXT NOT NULL, ALGORITHM=INSTANT"
text Keluaran 
ERROR 1846 (0A000) at line 1: ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.

Error 1846 muncul sebelum satu baris pun disentuh, dan definisi tabel tidak berubah. Pesannya menunjukkan jalan keluarnya, yaitu menjalankan perubahan tipe tersebut dengan ALGORITHM=INPLACE atau COPY. Pola semacam ini dipakai pada produksi untuk memastikan perubahan besar benar-benar berjalan dengan metode yang diperhitungkan.

Menghapus kolom#

Penghapusan kolom dilakukan dengan DROP COLUMN, dan datanya ikut hilang bersama kolomnya. Operasi ini tidak dapat dibatalkan, sehingga kolom yang masih mungkin diperlukan sebaiknya diberi tanda usang lebih dahulu, misalnya diubah namanya, sebelum benar-benar dihapus pada rilis berikutnya.

bash 
# menghapus kolom catatan yang tidak lagi dipakai
mysql -u root perpustakaan -e "ALTER TABLE anggota DROP COLUMN catatan"
bash 
# memeriksa tabel akhir setelah seluruh rangkaian perubahan
mysql -t -u root perpustakaan -e "SELECT id, nama, keanggotaan FROM anggota ORDER BY id LIMIT 3"
text Keluaran 
+----+-----------+-------------+
| id | nama      | keanggotaan |
+----+-----------+-------------+
|  1 | Anggota 1 | aktif       |
|  2 | Anggota 2 | aktif       |
|  3 | Anggota 3 | aktif       |
+----+-----------+-------------+

Tabel akhir memuat kolom id, nama, email, dan keanggotaan, persis struktur yang dirancang pada akhir rangkaian perubahan.

Kesalahan umum#

Kesalahan pertama adalah menjalankan ALTER TABLE pada tabel raksasa tanpa pengujian terlebih dahulu. Salin tabel ke lingkungan latihan, ukur lamanya, dan tentukan klausul ALGORITHM serta LOCK yang disengaja sebelum pernyataan yang sama disentuh ke produksi. Perubahan yang membangun ulang tabel juga membutuhkan ruang disk tambahan hingga dua kali ukuran tabel selama prosesnya.

Kesalahan kedua adalah mengganti nama kolom melalui CHANGE COLUMN dengan menyalin ulang seluruh definisinya. Satu kesalahan ketik pada definisi dapat mengubah tipe kolom tanpa sengaja, sedangkan RENAME COLUMN menyatakan niat penggantian nama saja.

Kesalahan ketiga adalah menganggap semua operasi mendukung INSTANT. Operasi metadata seperti penambahan kolom di akhir tabel memang sekejap, tetapi perubahan tipe dan penghapusan kolom pada banyak versi menuntut pembangunan ulang. Tanyakan dukungannya pada dokumentasi versi yang berjalan, dan biarkan klausul ALGORITHM yang menyampaikannya secara eksplisit.

Ringkasan#

  • ALTER TABLE mengubah struktur tabel yang sudah terisi, dan beberapa operasi dapat digabungkan dalam satu pernyataan.
  • Penambahan kolom dikerjakan dengan ALGORITHM=INPLACE, LOCK=NONE agar tabel tetap melayani penulisan selama perubahan.
  • Klausul ALGORITHM menjadi rem pengaman, dan server menolak dengan error 1846 apabila algoritme yang diminta tidak didukung operasinya.
  • RENAME COLUMN mengganti nama tanpa menyentuh data, DROP COLUMN menghapus kolom beserta isinya, dan SHOW CREATE TABLE memverifikasi hasil setiap tahap.

Referensi#