Topik ini menjelaskan cara menggunakan sintaksis CREATE TABLE di AnalyticDB for MySQL untuk membuat tabel partisi dan tabel replikasi serta mendefinisikan kunci distribusi, kunci partisi, indeks, siklus hidup, dan penyimpanan berjenjangnya.
Sintaksis
CREATE TABLE [IF NOT EXISTS] table_name
({column_name column_type [column_attributes] [ column_constraints ] [COMMENT 'column_comment']
| table_constraints}
[, ... ])
[table_attribute]
[partition_options]
[index_all]
[storage_policy]
[block_size]
[engine]
[table_properties]
[AS query_expr]
[COMMENT 'table_comment']
column_attributes:
[DEFAULT {constant | CURRENT_TIMESTAMP}]
[AUTO_INCREMENT]
column_constraints:
[{NOT NULL|NULL} ]
[PRIMARY KEY]
table_constraints:
[{INDEX|KEY} [index_name] (column_name|column_name->'$.json_path'|column_name->'$[*]')][,...]
[FULLTEXT [INDEX|KEY] [index_name] (column_name) [index_option]] [,...]
[PRIMARY KEY [index_name] (column_name,...)]
[CLUSTERED KEY [index_name] (column_name[ASC|DESC],...) ]
[[CONSTRAINT [symbol]] FOREIGN KEY (fk_column_name) REFERENCES pk_table_name (pk_column_name)][,...]
[ANN INDEX [index_name] (column_name,...) [index_option]] [,...]
table_attribute:
DISTRIBUTED BY HASH(column_name,...) | DISTRIBUTED BY BROADCAST
partition_options:
PARTITION BY
{VALUE(column_name) | VALUE(DATE_FORMAT(column_name, 'format')) | VALUE(FROM_UNIXTIME(column_name, 'format'))}
LIFECYCLE N
index_all:
INDEX_ALL= 'Y|N'
storage_policy:
STORAGE_POLICY= {'HOT'|'COLD'|'MIXED' {hot_partition_count=N}}
block_size:
BLOCK_SIZE= VALUE
engine:
ENGINE= 'XUANWU|XUANWU_V2'Tabel internal di AnalyticDB for MySQL menggunakan algoritma kompresi zstd secara default.
Parameter
Parameter
Contoh
Partisi otomatis berdasarkan tanggal
Buat tabel partisi bernama sales yang secara otomatis dipartisi berdasarkan tanggal di kolom sale_time.
CREATE TABLE sales (
sale_id BIGINT NOT NULL COMMENT 'ID Pesanan',
customer_id VARCHAR NOT NULL COMMENT 'ID Pelanggan',
phone_num BIGINT NOT NULL COMMENT 'Nomor telepon pelanggan',
revenue DECIMAL(15, 2) COMMENT 'Jumlah pesanan',
sale_time TIMESTAMP NOT NULL COMMENT 'Waktu pesanan',
PRIMARY KEY (sale_time,sale_id)
)
DISTRIBUTED BY HASH(sale_id)
PARTITION BY VALUE(DATE_FORMAT(sale_time, '%Y%m%d')); Tabel partisi dengan siklus hidup
Buat tabel partisi bernama customer. Kolom login_time, customer_id, dan phone_num membentuk kunci primer gabungan. kunci distribusi adalah customer_id, kunci partisi adalah login_time, dan siklus hidup diatur ke 30.
Partisi diurutkan dalam urutan descending berdasarkan kunci partisi login_time (misalnya, 20231202, 20231201). Sistem hanya menyimpan 30 partisi dengan nilai kunci terbesar. Saat data ditulis ke partisi ke-31, sistem secara otomatis menghapus partisi dengan nilai kunci terkecil.
Misalnya, asumsikan data ditulis dengan login_time 20231201 pada hari ke-1, 20231202 pada hari ke-2, dan seterusnya. Saat data dengan login_time 20231231 ditulis pada hari ke-31, sistem secara otomatis menghapus partisi dengan data terlama ('20231201'). Hal ini mempertahankan hanya data dari 30 hari terakhir.
CREATE TABLE customer (
customer_id BIGINT NOT NULL COMMENT 'ID Pelanggan',
customer_name VARCHAR NOT NULL COMMENT 'Nama pelanggan',
phone_num BIGINT NOT NULL COMMENT 'Nomor telepon',
city_name VARCHAR NOT NULL COMMENT 'Kota',
sex INT NOT NULL COMMENT 'Jenis kelamin',
id_number VARCHAR NOT NULL COMMENT 'Nomor KTP',
home_address VARCHAR NOT NULL COMMENT 'Alamat rumah',
office_address VARCHAR NOT NULL COMMENT 'Alamat kantor',
age INT NOT NULL COMMENT 'Usia',
login_time TIMESTAMP NOT NULL COMMENT 'Waktu login',
PRIMARY KEY (login_time,customer_id,phone_num)
)
DISTRIBUTED BY HASH(customer_id)
PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m%d')) LIFECYCLE 30
COMMENT 'Tabel informasi pelanggan'; Tabel non-partisi
Perilaku default: Kunci primer sebagai kunci distribusi
Jika Anda mendefinisikan kunci primer untuk tabel tetapi tidak menentukan kunci distribusi, AnalyticDB for MySQL menggunakan kunci primer sebagai kunci distribusi secara default.
CREATE TABLE orders (
order_id BIGINT NOT NULL COMMENT 'ID Pesanan',
customer_id INT NOT NULL COMMENT 'ID Pelanggan',
order_status VARCHAR(1) NOT NULL COMMENT 'Status pesanan',
total_price DECIMAL(15, 2) NOT NULL COMMENT 'Jumlah pesanan',
order_date DATE NOT NULL COMMENT 'Tanggal pesanan',
PRIMARY KEY(order_id,order_date)
);Jalankan pernyataan SHOW CREATE TABLE untuk memverifikasi hasilnya. Output menunjukkan bahwa kolom kunci primer order_id dan order_date juga berfungsi sebagai kunci distribusi.
SHOW CREATE TABLE orders;+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE `orders` ( |
| | `order_id` bigint NOT NULL COMMENT 'ID Pesanan', |
| | `customer_id` int NOT NULL COMMENT 'ID Pelanggan', |
| | `order_status` varchar(1) NOT NULL COMMENT 'Status pesanan', |
| | `total_price` decimal(15, 2) NOT NULL COMMENT 'Jumlah pesanan', |
| | `order_date` date NOT NULL COMMENT 'Tanggal pesanan', |
| | PRIMARY KEY (`order_id`,`order_date`) |
| | ) DISTRIBUTED BY HASH(`order_id`,`order_date`) INDEX_ALL='Y' STORAGE_POLICY='HOT' ENGINE='XUANWU' TABLE_PROPERTIES='{"format":"columnstore"}' |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)Perilaku default: Kunci primer dan distribusi ditambahkan otomatis
Jika Anda membuat tabel tanpa menentukan kunci primer atau kunci distribusi, AnalyticDB for MySQL secara otomatis menambahkan kolom bernama __adb_auto_id__ dan menggunakannya sebagai kunci primer sekaligus kunci distribusi.
CREATE TABLE orders_new (
order_id BIGINT NOT NULL COMMENT 'ID Pesanan',
customer_id INT NOT NULL COMMENT 'ID Pelanggan',
order_status VARCHAR(1) NOT NULL COMMENT 'Status pesanan',
total_price DECIMAL(15, 2) NOT NULL COMMENT 'Jumlah pesanan',
order_date DATE NOT NULL COMMENT 'Tanggal pesanan'
);Jalankan pernyataan SHOW CREATE TABLE untuk memverifikasi hasilnya. Output menunjukkan bahwa AnalyticDB for MySQL menambahkan kolom auto-increment bernama __adb_auto_id__ dan menetapkannya sebagai kunci primer sekaligus kunci distribusi.
SHOW CREATE TABLE orders_new;+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| orders_new | CREATE TABLE `orders_new` ( |
| | `__adb_auto_id__` bigint AUTO_INCREMENT, |
| | `order_id` bigint NOT NULL COMMENT 'ID Pesanan', |
| | `customer_id` int NOT NULL COMMENT 'ID Pelanggan', |
| | `order_status` varchar(1) NOT NULL COMMENT 'Status pesanan', |
| | `total_price` decimal(15, 2) NOT NULL COMMENT 'Jumlah pesanan', |
| | `order_date` date NOT NULL COMMENT 'Tanggal pesanan', |
| | PRIMARY KEY (`__adb_auto_id__`) |
| | ) DISTRIBUTED BY HASH(`__adb_auto_id__`) INDEX_ALL='Y' STORAGE_POLICY='HOT' ENGINE='XUANWU' TABLE_PROPERTIES='{"format":"columnstore"}' |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)Tentukan kunci primer dan distribusi
Buat tabel bernama supplier. Kolom supplier_id adalah kolom auto-increment. kunci distribusi adalah supplier_id, dan data di-shard dengan meng-hash nilai supplier_id.
CREATE TABLE supplier (
supplier_id BIGINT AUTO_INCREMENT PRIMARY KEY,
supplier_name VARCHAR,
address INT,
phone VARCHAR
)
DISTRIBUTED BY HASH(supplier_id);Kebijakan penyimpanan berjenjang
Kebijakan Cold Storage
CREATE TABLE item (
order_id BIGINT NOT NULL,
item_id INT NOT NULL,
quantity DECIMAL(15, 2) NOT NULL,
discount DECIMAL(15, 2) NOT NULL,
shipdate DATE NOT NULL,
PRIMARY KEY (order_id,item_id,shipdate)
)
DISTRIBUTED BY HASH(item_id)
PARTITION BY VALUE(date_format(shipdate, '%Y%m')) LIFECYCLE 200
STORAGE_POLICY='COLD';Kebijakan penyimpanan hot
CREATE TABLE item (
order_id BIGINT NOT NULL,
item_id INT NOT NULL,
quantity DECIMAL(15, 2) NOT NULL,
discount DECIMAL(15, 2) NOT NULL,
shipdate DATE NOT NULL,
PRIMARY KEY (order_id,item_id,shipdate)
)
DISTRIBUTED BY HASH(item_id)
PARTITION BY VALUE(date_format(shipdate, '%Y%m')) LIFECYCLE 200
STORAGE_POLICY='HOT';Kebijakan campuran dengan 16 partisi hot
CREATE TABLE item (
order_id BIGINT NOT NULL,
item_id INT NOT NULL,
quantity DECIMAL(15, 2) NOT NULL,
discount DECIMAL(15, 2) NOT NULL,
shipdate DATE NOT NULL,
PRIMARY KEY (order_id,item_id,shipdate)
)
DISTRIBUTED BY HASH(item_id)
PARTITION BY VALUE(date_format(shipdate, '%Y%m')) LIFECYCLE 200
STORAGE_POLICY='MIXED' HOT_PARTITION_COUNT=16;Buat indeks biasa pada kolom tertentu
Contoh ini membuat indeks biasa pada kolom id dan date.
CREATE TABLE index_tb (
id INT,
sales DECIMAL(15, 2),
date DATE,
INDEX (id),
INDEX (date),
PRIMARY KEY (id)
)
DISTRIBUTED BY HASH(id);Definisikan indeks terkluster
Buat indeks terkluster bernama clustered_index pada kolom quantity.
CREATE TABLE clustered (
product_id INT,
product_name VARCHAR,
quantity INT,
price DECIMAL(10, 2),
CLUSTERED KEY INDEX clustered_index(quantity)
)
DISTRIBUTED BY HASH(product_id);Indeks teks penuh
Buat indeks teks penuh bernama fidx_c pada kolom content.
CREATE TABLE fulltext_tb (
id INT,
content VARCHAR,
keyword VARCHAR,
FULLTEXT INDEX fidx_c(content),
PRIMARY KEY (id)
)
DISTRIBUTED BY HASH(id);Untuk informasi lebih lanjut tentang membuat dan memodifikasi indeks teks penuh, lihat Buat indeks teks penuh.
Untuk informasi lebih lanjut, lihat Pencarian teks penuh.
Definisikan indeks vektor
Definisikan short_feature (tipe array<smallint>) dan float_feature (tipe array<float>) sebagai kolom vektor dengan dimensi 4.
Buat indeks vektor short_feature_index pada kolom short_feature dan float_feature_index pada kolom float_feature.
CREATE TABLE fact_tb (
xid BIGINT NOT NULL,
cid BIGINT NOT NULL,
uid VARCHAR NOT NULL,
vid VARCHAR NOT NULL,
wid VARCHAR NOT NULL,
short_feature array<smallint>(4),
float_feature array<float>(4),
ann index short_feature_index(short_feature),
ann index float_feature_index(float_feature),
PRIMARY KEY (xid, cid, vid)
)
DISTRIBUTED BY HASH(xid) PARTITION BY VALUE(cid) LIFECYCLE 4;Untuk detail tentang pengindeksan vektor dan pencarian vektor, lihat Pencarian vektor.
Definisikan kunci asing
Buat tabel bernama store_returns. Gunakan klausa FOREIGN KEY untuk mengaitkan kolom sr_item_sk dengan kunci primer customer_id dari tabel customer.
CREATE TABLE store_returns (
sr_sale_id BIGINT NOT NULL PRIMARY KEY,
sr_store_sk BIGINT,
sr_item_sk BIGINT NOT NULL,
FOREIGN KEY (sr_item_sk) REFERENCES customer (customer_id)
);Indeks array JSON
Buat indeks array JSON bernama idx_vj pada kolom vj.
CREATE TABLE json(
id INT,
vj JSON,
INDEX idx_vj(vj->'$[*]')
)
DISTRIBUTED BY HASH(id);Untuk detail tentang membuat dan memodifikasi indeks array JSON, lihat Buat indeks array JSON dan Indeks array JSON.
FAQ
Atribut dan kendala kolom
Kunci distribusi, kunci partisi, dan siklus hidup
Indeks
Penyimpanan berorientasi kolom
Lainnya
Error umum
Topik terkait
-
Untuk menulis data ke tabel, lihat INSERT INTO.
-
Untuk memasukkan atau menimpa data di tabel dengan hasil kueri, lihat INSERT SELECT FROM atau INSERT OVERWRITE SELECT.
-
Untuk mengimpor data dari sumber data seperti ApsaraDB RDS, MaxCompute, dan OSS, lihat impor data.