MySQL - 创建表

在 MySQL 这样的关系型数据库中，表（tables）是用于存储数据的基本结构。每个表都由列（columns，也称字段 fields）和行（rows，也称记录 records）组成。本教程将介绍 CREATE TABLE 语句、最佳实践以及定义数据结构的现代技术。

`CREATE TABLE` 语句

CREATE TABLE 语句用于在数据库中定义新表。创建表时，您必须指定表的名称、列的名称以及每列的数据类型（data type）。

### 表设计的关键考量

*   **表和列命名**：选择描述性强且一致的名称。表的常用命名约定是使用 `snake_case`（例如，`user_profiles`）作为表名和列名。
*   **数据类型**：选择正确的数据类型对于数据完整性和性能至关重要。使用最适合您数据的特定类型（例如，整数使用 `INT`，货币使用 `DECIMAL`，可变长度字符串使用 `VARCHAR`，时间戳使用 `TIMESTAMP`）。
*   **主键**：每个表都应该有一个主键（primary key），它是一个（或一组）唯一标识每行的列。
*   **存储引擎**：现代 MySQL 默认使用 `InnoDB` 存储引擎，它提供了事务（transactions）、外键（foreign keys）和崩溃恢复（crash recovery）等基本功能。它是大多数应用程序的推荐选择。

语法

创建表的基本语法如下：

CREATE TABLE [IF NOT EXISTS] table_name (
    column1_name data_type [CONSTRAINTS],
    column2_name data_type [CONSTRAINTS],
    ...,
    PRIMARY KEY (column_name)
) ENGINE=InnoDB CHARACTER SET=utf8mb4;

示例：创建一个 `users` 表

让我们创建一个包含常见字段和约束的现代化 users 表。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

让我们分解这个示例的组成部分：

id INT AUTO_INCREMENT PRIMARY KEY：一个自动为每个用户生成新的唯一 ID 的整数主键。
VARCHAR(50) NOT NULL UNIQUE：一个用于用户名的可变长度字符串，它不能为空（NOT NULL），并且在所有行中必须是唯一的（UNIQUE）。
password_hash VARCHAR(255)：一个用于存储哈希密码的字段。切勿存储纯文本密码！
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP：一个时间戳（timestamp），自动记录行创建的时间。
updated_at ... ON UPDATE CURRENT_TIMESTAMP：一个时间戳，每当行被修改时自动更新为当前时间。

验证

创建表后，您可以使用 DESCRIBE（或 DESC）命令验证其结构：

DESCRIBE users;

字段	类型	空	键	默认值	额外信息
id	int	NO	PRI	NULL	auto_increment
username	varchar(50)	NO	UNI	NULL
email	varchar(100)	NO	UNI	NULL
password_hash	varchar(255)	NO		NULL
full_name	varchar(100)	YES		NULL
created_at	timestamp	NO		CURRENT_TIMESTAMP	DEFAULT_GENERATED
updated_at	timestamp	NO		CURRENT_TIMESTAMP	on update CURRENT_TIMESTAMP

使用 `IF NOT EXISTS`

如果您尝试创建一个已存在的表，MySQL 将抛出错误。为避免这种情况，尤其是在安装脚本中，请使用 IF NOT EXISTS 子句。

CREATE TABLE IF NOT EXISTS users (
    -- ... 列定义 ...
);
-- 如果表已存在，此查询不执行任何操作并返回警告。
-- 如果表不存在，则创建该表。

从现有表创建表（CTAS）

您可以使用 CREATE TABLE ... AS SELECT (CTAS) 语句从现有表创建新表并用数据填充它。这对于创建备份、摘要或数据子集非常有用。

语法

CREATE TABLE new_table_name AS
SELECT column1, column2, ...
FROM existing_table_name
[WHERE condition];

重要提示： 新表将继承列名和数据类型，但不会继承 PRIMARY KEY（主键）、FOREIGN KEY（外键）、UNIQUE（唯一）等约束或 AUTO_INCREMENT（自增）等属性。您必须在创建后使用 ALTER TABLE 手动添加这些。

示例：创建备份

让我们创建 users 表的备份。

CREATE TABLE users_backup_2024 AS SELECT * FROM users;

这将创建一个新表 users_backup_2024，其中包含 users 表在该时刻数据的精确副本。

以编程方式创建表

以编程方式创建表在应用程序设置、数据库迁移和测试中很常见。下面是各种语言的现代示例。

PHP（使用 PDO）
Node.js（使用 mysql2/promise）
Java（使用 JDBC）
Python（使用 mysql-connector-python）



```php
<?php
// createTable.php
$host = 'localhost'; $db = 'test_db'; $user = 'root'; $pass = 'password';
$dsn = "mysql:host=$host;dbname=$db;charset=utf8mb4";
$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Connected successfully.\n";

    $sql = "CREATE TABLE IF NOT EXISTS products (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        price DECIMAL(10, 2) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB;";

    $pdo->exec($sql);
    echo "Table 'products' created successfully or already exists.\n";

} catch (PDOException $e) {
    die("Could not connect or create table: " . $e->getMessage());
}

输出： Connected successfully. Table 'products' created successfully or already exists.

// 先决条件: npm install mysql2 dotenv
require('dotenv').config();
const mysql = require('mysql2/promise');

async function main() {
  let connection;
  try {
    connection = await mysql.createConnection({
        host: process.env.DB_HOST, user: process.env.DB_USER,
        password: process.env.DB_PASSWORD, database: 'test_db'
    });
    console.log('Connected successfully.');

    const createTableSql = `
      CREATE TABLE IF NOT EXISTS tutorials (
        tutorial_id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(100) NOT NULL,
        author VARCHAR(40) NOT NULL,
        submission_date DATE
      );
    `;

    await connection.query(createTableSql);
    console.log("Table 'tutorials' created successfully or already exists.");

  } catch (err) {
    console.error(`Error: ${err.message}`);
  } finally {
    if (connection) await connection.end();
  }
}

main();

输出： Connected successfully. Table 'tutorials' created successfully or already exists.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class CreateTable {
    static final String DB_URL = "jdbc:mysql://localhost/test_db";
    static final String USER = "root";
    static final String PASS = "password";

    public static void main(String[] args) {
        String sql = "CREATE TABLE IF NOT EXISTS employees (" +
                     "id INT AUTO_INCREMENT PRIMARY KEY, " +
                     "first_name VARCHAR(50), " +
                     "last_name VARCHAR(50), " +
                     "email VARCHAR(100) NOT NULL UNIQUE)";

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             Statement stmt = conn.createStatement()) {
            System.out.println("Connected successfully...");
            stmt.executeUpdate(sql);
            System.out.println("Table 'employees' created or already exists.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

输出： Connected successfully... Table 'employees' created or already exists.

import mysql.connector
from mysql.connector import errorcode

config = {'user': 'root', 'password': 'password', 'host': '127.0.0.1', 'database': 'test_db'}
TABLES = {}
TABLES['tutorials'] = (
    "CREATE TABLE `tutorials` ("
    "  `tutorial_id` int(11) NOT NULL AUTO_INCREMENT,"
    "  `title` varchar(100) NOT NULL,"
    "  `author` varchar(40) NOT NULL,"
    "  `submission_date` date DEFAULT NULL,"
    "  PRIMARY KEY (`tutorial_id`)"
    ") ENGINE=InnoDB")

try:
    cnx = mysql.connector.connect(**config)
    cursor = cnx.cursor()
    print("Connected successfully.")

    cursor.execute(TABLES['tutorials'])
    print("Table 'tutorials' created successfully.")

except mysql.connector.Error as err:
    if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
        print("Table 'tutorials' already exists.")
    else:
        print(err.msg)
finally:
    if 'cursor' in locals(): cursor.close()
    if 'cnx' in locals() and cnx.is_connected(): cnx.close()

输出： Connected successfully. Table 'tutorials' created successfully.

MySQL - 创建表

MySQL - 创建表

CREATE TABLE 语句

语法

示例：创建一个 users 表

验证

使用 IF NOT EXISTS

从现有表创建表（CTAS）

语法

示例：创建备份

以编程方式创建表

`CREATE TABLE` 语句

示例：创建一个 `users` 表

使用 `IF NOT EXISTS`