📋 MySQL 创建数据表

-- 创建表的基本语法 CREATE TABLE table_name ( column1 datatype constraints, column2 datatype constraints, column3 datatype constraints, ... table_constraints ); -- 简单示例 CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name ( column_definition, ... [table_constraint] ) [ENGINE=engine_name] [CHARACTER SET charset_name] [COLLATE collation_name] [COMMENT='table_comment'];

-- 主键约束 id INT AUTO_INCREMENT PRIMARY KEY -- 非空约束 username VARCHAR(50) NOT NULL -- 唯一约束 email VARCHAR(100) UNIQUE -- 默认值 status VARCHAR(20) DEFAULT 'active' created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 检查约束（MySQL 8.0.16+） age INT CHECK (age >= 0 AND age <= 150) -- 外键约束 user_id INT, FOREIGN KEY (user_id) REFERENCES users(id)

CREATE TABLE orders ( id INT AUTO_INCREMENT, user_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, order_date DATE NOT NULL, total_amount DECIMAL(10,2) NOT NULL, -- 主键约束 PRIMARY KEY (id), -- 外键约束 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT, -- 唯一约束 UNIQUE KEY unique_user_product (user_id, product_id, order_date), -- 检查约束 CHECK (quantity > 0), CHECK (total_amount >= 0) );

CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID', username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名', email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱', password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希', first_name VARCHAR(50) COMMENT '名', last_name VARCHAR(50) COMMENT '姓', phone VARCHAR(20) COMMENT '电话号码', birth_date DATE COMMENT '出生日期', gender ENUM('M', 'F', 'Other') COMMENT '性别', status ENUM('active', 'inactive', 'suspended') DEFAULT 'active' COMMENT '状态', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', INDEX idx_username (username), INDEX idx_email (email), INDEX idx_status (status), INDEX idx_created_at (created_at) ) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(200) NOT NULL COMMENT '商品名称', description TEXT COMMENT '商品描述', category_id INT NOT NULL COMMENT '分类ID', brand VARCHAR(100) COMMENT '品牌', model VARCHAR(100) COMMENT '型号', price DECIMAL(10,2) NOT NULL COMMENT '价格', cost DECIMAL(10,2) COMMENT '成本', stock_quantity INT DEFAULT 0 COMMENT '库存数量', weight DECIMAL(8,3) COMMENT '重量(kg)', dimensions VARCHAR(50) COMMENT '尺寸', color VARCHAR(30) COMMENT '颜色', material VARCHAR(100) COMMENT '材质', warranty_period INT COMMENT '保修期(月)', is_active BOOLEAN DEFAULT TRUE COMMENT '是否启用', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (category_id) REFERENCES categories(id), INDEX idx_name (name), INDEX idx_category (category_id), INDEX idx_brand (brand), INDEX idx_price (price), INDEX idx_stock (stock_quantity), INDEX idx_active (is_active) ) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, order_number VARCHAR(50) NOT NULL UNIQUE COMMENT '订单号', user_id INT NOT NULL COMMENT '用户ID', status ENUM('pending', 'confirmed', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending', total_amount DECIMAL(12,2) NOT NULL COMMENT '总金额', shipping_fee DECIMAL(8,2) DEFAULT 0 COMMENT '运费', tax_amount DECIMAL(10,2) DEFAULT 0 COMMENT '税费', discount_amount DECIMAL(10,2) DEFAULT 0 COMMENT '折扣金额', payment_method ENUM('credit_card', 'debit_card', 'paypal', 'cash') COMMENT '支付方式', payment_status ENUM('pending', 'paid', 'failed', 'refunded') DEFAULT 'pending', shipping_address TEXT COMMENT '收货地址', billing_address TEXT COMMENT '账单地址', notes TEXT COMMENT '备注', order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间', shipped_date TIMESTAMP NULL COMMENT '发货时间', delivered_date TIMESTAMP NULL COMMENT '送达时间', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id), INDEX idx_order_number (order_number), INDEX idx_user_id (user_id), INDEX idx_status (status), INDEX idx_payment_status (payment_status), INDEX idx_order_date (order_date) ) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 按日期分区 CREATE TABLE sales_data ( id INT AUTO_INCREMENT, sale_date DATE NOT NULL, amount DECIMAL(10,2), customer_id INT, PRIMARY KEY (id, sale_date) ) PARTITION BY RANGE (YEAR(sale_date)) ( PARTITION p2020 VALUES LESS THAN (2021), PARTITION p2021 VALUES LESS THAN (2022), PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN (2024), PARTITION p_future VALUES LESS THAN MAXVALUE ); -- 按哈希分区 CREATE TABLE user_logs ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, action VARCHAR(100), log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) PARTITION BY HASH(user_id) PARTITIONS 4;

-- 创建临时表 CREATE TEMPORARY TABLE temp_calculations ( id INT AUTO_INCREMENT PRIMARY KEY, value1 DECIMAL(10,2), value2 DECIMAL(10,2), result DECIMAL(10,2) ); -- 临时表只在当前会话中存在 -- 会话结束时自动删除

-- 指定存储引擎 CREATE TABLE fast_cache ( id INT PRIMARY KEY, data VARCHAR(255) ) ENGINE=Memory; CREATE TABLE archive_logs ( id INT AUTO_INCREMENT PRIMARY KEY, log_data TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=Archive;

-- 指定字符集和排序规则 CREATE TABLE multilingual_content ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200), content TEXT, language CHAR(2) ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 为特定列指定不同的字符集 CREATE TABLE mixed_charset ( id INT PRIMARY KEY, ascii_data VARCHAR(100) CHARACTER SET ascii, utf8_data VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, binary_data VARBINARY(255) );

-- 错误信息 ERROR 1050 (42S01): Table 'table_name' already exists -- 解决方案：使用 IF NOT EXISTS CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL );

-- 错误信息 ERROR 1215 (HY000): Cannot add foreign key constraint -- 解决方案：确保引用的表和列存在，数据类型匹配 -- 1. 先创建被引用的表 CREATE TABLE categories ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL ); -- 2. 再创建引用表 CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, category_id INT, FOREIGN KEY (category_id) REFERENCES categories(id) );