Databend 九月月报：自增列 AUTOINCREMENT 与行级安全

-- 定义策略：每个租户只能看自己的数据CREATE ROW ACCESS POLICY tenant_isolation AS (tenant_id INT)RETURNS BOOLEAN -> tenant_id = CURRENT_TENANT();
-- 加到表上ALTER TABLE customer_data    ADD ROW ACCESS POLICY tenant_isolation(tenant_id);
-- 以后所有查询都自动过滤SELECT * FROM customer_data;-- 用户只能看到自己租户的数据,想越权都不行

CREATE SEQUENCE user_id_seq START 1 INCREMENT 1;CREATE TABLE users (id INT DEFAULT NEXTVAL('user_id_seq'), name VARCHAR);-- 删表时还得记得删 Sequence,容易忘

CREATE TABLE users (    id INT AUTOINCREMENT,    name VARCHAR,    email VARCHAR);
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');-- ID 自动从 0 开始：0, 1, 2...

-- 最简单的id INT AUTOINCREMENT
-- 从 1000 开始,每次加 10order_id INT AUTOINCREMENT (1000, 10)  -- 1000, 1010, 1020...
-- MySQL 风格的写法也支持invoice_id INT IDENTITY START 1000 INCREMENT 10
-- 一张表可以有多个自增列CREATE TABLE events (    event_id INT AUTOINCREMENT,    sequence_num INT AUTOINCREMENT,    data VARCHAR);

CREATE TABLE products (    id INT AUTOINCREMENT,    name VARCHAR NOT NULL,    price DECIMAL(10,2),    discount_percent INT,    stock INT,
    CONSTRAINT price_valid CHECK (price > 0),    CONSTRAINT discount_range CHECK (discount_percent BETWEEN 0 AND 100),    CONSTRAINT stock_non_negative CHECK (stock >= 0));
-- 正常数据,没问题INSERT INTO products (name, price, discount_percent, stock)VALUES ('Widget', 59.99, 10, 100);
-- 不符合规则的数据,直接拒绝INSERT INTO products (name, price, discount_percent, stock)VALUES ('Gadget', -10.00, 150, -5);-- Error: CHECK constraint failed

-- 传统方法:两步RENAME中间有间隙ALTER TABLE orders RENAME TO orders_backup;  -- 第1步ALTER TABLE orders_v2 RENAME TO orders;      -- 第2步-- ⚠️ 这两步之间orders不存在,应用查询会失败

-- 1. 创建新结构的表CREATE TABLE orders_v2 (    order_id INT,    user_id INT,    amount DECIMAL(10,2),    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 新增列    status VARCHAR DEFAULT 'pending'                  -- 新增列);
-- 2. 离线迁移数据INSERT INTO orders_v2 SELECT *, CURRENT_TIMESTAMP, 'completed' FROM orders;
-- 3. 单事务内原子交换ALTER TABLE orders SWAP WITH orders_v2;-- ✓ orders和orders_v2在同一事务内互换名字-- ✓ 应用全程可以访问orders,零停机

SHOW TABLE;-- 以前: "unexpected TABLE, expecting STATUS, TABLES..."-- 现在: "Did you mean SHOW TABLES or SHOW TABLE FUNCTIONS?"
VACUUM TEMPARE FILES;-- 提示: "Did you mean VACUUM TEMPORARY FILES?"