mysql中的默认约束

-- 创建表时指定默认约束
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT '待处理'
);

-- 插入数据时未指定某些列的值
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');

-- 查询结果
SELECT * FROM users;

-- 确保插入数据时没有为该列提供NULL值
INSERT INTO users (username, email, created_at, status) VALUES ('john_doe', 'john@example.com', NULL, NULL);

-- 检查默认值设置是否正确
ALTER TABLE users ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE users ALTER COLUMN status SET DEFAULT '待处理';

-- 使用函数作为默认值
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_DATE,
    total_amount DECIMAL(10, 2) DEFAULT (SELECT AVG(total_amount) FROM orders WHERE user_id = NEW.user_id)
);