MySQL 创建数据表

在MySQL中，数据表是存储数据的基本单位。创建数据表是数据库设计的关键步骤，需要仔细规划表结构、字段类型和约束条件。

CREATE TABLE 基本语法

完整语法结构

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name (
    column_name data_type [NOT NULL | NULL] [DEFAULT default_value]
    [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
    [COMMENT 'string']
    [column_constraint],
    [table_constraint],
    ...
) [ENGINE=engine_name] [DEFAULT CHARSET=charset_name] [COLLATE=collation_name]
  [COMMENT='table_comment'];

主要参数说明

基本表创建示例

简单的用户表

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

包含更多字段的产品表

CREATE TABLE products (
    product_id INT NOT NULL AUTO_INCREMENT,
    product_name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    category_id INT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

使用命令行创建数据表

登录MySQL并选择数据库

mysql -u root -p
Enter password: ******

mysql> USE TUTORIALS;
Database changed

创建 tutorials_tbl 表

CREATE TABLE tutorials_tbl (
    tutorial_id INT NOT NULL AUTO_INCREMENT,
    tutorial_title VARCHAR(100) NOT NULL,
    tutorial_author VARCHAR(40) NOT NULL,
    submission_date DATE,
    PRIMARY KEY (tutorial_id)
);

验证表创建

-- 查看表结构
DESC tutorials_tbl;

-- 或者使用
DESCRIBE tutorials_tbl;

-- 查看更详细的表信息
SHOW CREATE TABLE tutorials_tbl;

输出结果示例：

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| tutorial_id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| tutorial_title   | varchar(100) | NO   |     | NULL    |                |
| tutorial_author  | varchar(40)  | NO   |     | NULL    |                |
| submission_date  | date         | YES  |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+

高级表创建功能

设置外键约束

CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

创建复合主键

CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

设置检查约束（MySQL 8.0+）

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    salary DECIMAL(10, 2) NOT NULL,
    department ENUM('HR', 'IT', 'Finance', 'Marketing') NOT NULL,
    hire_date DATE NOT NULL,
    CONSTRAINT chk_salary CHECK (salary > 0),
    CONSTRAINT chk_hire_date CHECK (hire_date >= '2000-01-01')
);

使用 PHP 脚本创建数据表

使用 MySQLi 面向对象方式

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "TUTORIALS";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 创建表的SQL语句
$sql = "CREATE TABLE tutorials_tbl (
    tutorial_id INT NOT NULL AUTO_INCREMENT,
    tutorial_title VARCHAR(100) NOT NULL,
    tutorial_author VARCHAR(40) NOT NULL,
    submission_date DATE,
    PRIMARY KEY (tutorial_id)
)";

if ($conn->query($sql) === TRUE) {
    echo "数据表 tutorials_tbl 创建成功";
} else {
    echo "创建数据表失败: " . $conn->error;
}

$conn->close();
?>

使用 MySQLi 过程式方式

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "TUTORIALS";

// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);

// 检查连接
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}

$sql = "CREATE TABLE IF NOT EXISTS users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
)";

if (mysqli_query($conn, $sql)) {
    echo "数据表 users 创建成功";
} else {
    echo "创建数据表失败: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

使用 PDO 方式

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "TUTORIALS";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // 设置PDO错误模式为异常
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 创建表的SQL语句
    $sql = "CREATE TABLE products (
        product_id INT NOT NULL AUTO_INCREMENT,
        product_name VARCHAR(255) NOT NULL,
        description TEXT,
        price DECIMAL(10, 2) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (product_id)
    )";

    // 使用exec()，因为没有结果返回
    $conn->exec($sql);
    echo "数据表 products 创建成功";

} catch(PDOException $e) {
    echo "错误: " . $e->getMessage();
}

$conn = null;
?>

表选项和存储引擎

常用存储引擎

完整的表创建示例

CREATE TABLE IF NOT EXISTS customers (
    customer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    address TEXT,
    city VARCHAR(50),
    country VARCHAR(50) DEFAULT 'China',
    postal_code VARCHAR(20),
    date_of_birth DATE,
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',

    PRIMARY KEY (customer_id),
    INDEX idx_email (email),
    INDEX idx_last_name (last_name),
    INDEX idx_city (city),
    INDEX idx_status (status)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='客户信息表'
  AUTO_INCREMENT=1001;

索引和性能优化

创建索引的多种方式

-- 在创建表时定义索引
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    category_id INT NOT NULL,
    price DECIMAL(10, 2),

    -- 唯一索引
    UNIQUE INDEX idx_product_name (product_name),

    -- 普通索引
    INDEX idx_category_id (category_id),

    -- 复合索引
    INDEX idx_category_price (category_id, price),

    -- 全文索引（MyISAM或InnoDB）
    FULLTEXT INDEX idx_product_desc (description)
);

索引最佳实践

• 为经常查询的字段创建索引
• 为外键字段创建索引
• 避免在频繁更新的字段上创建过多索引
• 使用复合索引来覆盖多个查询条件
• 定期分析和优化索引

表设计最佳实践

命名规范

• 使用有意义的表名和字段名
• 使用小写字母和下划线（snake_case）
• 避免使用MySQL保留字
• 保持命名一致性

数据类型选择

• 选择能满足需求的最小数据类型
• 为字符串字段指定合适的长度
• 使用适当的数据类型存储数字、日期等
• 考虑未来扩展性

约束和完整性

• 为每个表定义主键
• 使用外键维护引用完整性
• 设置NOT NULL约束确保数据完整性
• 使用DEFAULT值提供合理的默认数据

常见问题与解决方案

1. 表已存在错误

ERROR 1050 (42S01): Table 'tutorials_tbl' already exists

解决方案：使用CREATE TABLE IF NOT EXISTS或先删除已存在的表。

2. 语法错误

ERROR 1064 (42000): You have an error in your SQL syntax

解决方案：检查SQL语法，确保括号、逗号等符号正确。

3. 权限不足

ERROR 1142 (42000): CREATE command denied to user 'username'@'localhost' for table 'table_name'

解决方案：使用具有CREATE权限的用户。

4. 存储引擎不支持

ERROR 1286 (42000): Unknown storage engine 'InnoDB'

解决方案：检查MySQL版本和配置，确保存储引擎可用。

实用技巧和高级功能

基于现有表创建新表

-- 复制表结构（不包含数据）
CREATE TABLE new_users LIKE users;

-- 复制表结构和数据
CREATE TABLE users_backup AS SELECT * FROM users;

-- 复制部分数据到新表
CREATE TABLE active_users AS
SELECT * FROM users WHERE status = 'active';

创建临时表

-- 会话结束时自动删除
CREATE TEMPORARY TABLE temp_orders (
    order_id INT,
    customer_name VARCHAR(100),
    total_amount DECIMAL(10, 2)
);

表分区（MySQL 5.1+）

CREATE TABLE sales (
    sale_id INT AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    region VARCHAR(50),
    PRIMARY KEY (sale_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
);