【CS.DB】数据库-关系型数据库-MySQL-3.3.创建和管理表

1000.04.CS.DB-Database-Relational-MySQL-3.3.创建和管理表-Created: 2023-03-08.Thursday17:39

1. 创建和管理表

在 MySQL 中，创建和管理表是数据库操作的基础。以下是创建和管理表的主要步骤和方法。

1.1 定义表结构

定义表结构包括指定表的名称、列的名称和数据类型、主键和外键等。

示例：

CREATE TABLE example_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    description VARCHAR(100)
);

1.2 设置主键和外键

主键用于唯一标识表中的每一行记录，外键用于建立表之间的关系。

设置主键：

CREATE TABLE example_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

设置外键：

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

1.3 处理 NULL 值

在数据库设计和操作中，正确处理 NULL 值对于数据完整性和应用程序逻辑非常重要。

1.3.1 定义列时设置 NULL 或 NOT NULL

在创建表时，可以指定列是否允许 NULL 值：

CREATE TABLE example_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    description VARCHAR(100) NULL
);

1.3.2 插入数据时处理 NULL 值

在插入数据时，未指定值的列会默认设置为 NULL（如果允许 NULL）：

INSERT INTO example_table (name) VALUES ('Item 1');
-- description 列将被设置为 NULL

mysql> SELECT * FROM example_table;
+----+--------+-------------+
| id | name   | description |
+----+--------+-------------+
|  1 | Item 1 | NULL        |
|  2 | Item 2 | NULL        |
+----+--------+-------------+
2 rows in set (0.02 sec)

1.3.3 ERROR 1138 (22004): Invalid use of NULL value : 更新数据中的 NULL 值

当尝试修改列定义为 NOT NULL 时，如果表中存在 NULL 值，MySQL 会抛出 ERROR 1138 (22004): Invalid use of NULL value 错误。为了避免此错误，需要先将所有 NULL 值更新为非 NULL 值，然后再修改列定义。

mysql> ALTER TABLE test_table MODIFY COLUMN description VARCHAR(100) NOT NULL;
ERROR 1138 (22004): Invalid use of NULL value

在修改列为 NOT NULL 之前，需要将现有的 NULL 值更新为非 NULL 值：

UPDATE example_table SET description = '' WHERE description IS NULL;

1.3.4 ERROR 1364 (HY000): Field 'description' doesn't have a default value : 修改列定义为 NOT NULL 并设置默认值

在将列设置为 NOT NULL 后，如果在插入数据时没有为该列提供值，则会出现 ERROR 1364 (HY000): Field 'description' doesn't have a default value 错误。这是因为该列不允许 NULL 值，并且没有默认值。

mysql> INSERT INTO test_table (name) VALUES ('Item 4'), ('Item 3');
ERROR 1364 (HY000): Field 'description' doesn't have a default value

为了避免插入数据时未指定列值导致的错误，可以在修改列为 NOT NULL 时设置默认值：

ALTER TABLE example_table MODIFY COLUMN description VARCHAR(100) NOT NULL DEFAULT '';

1.3.5 查询时处理 NULL 值

在查询时，可以使用 IFNULL 或 COALESCE 函数处理 NULL 值：

SELECT id, name, IFNULL(description, 'No Description') AS description FROM example_table;

or

SELECT id, name, IFNULL(description, '') AS description FROM example_table;

1.3.6 NULL 值的比较

在 SQL 中，使用 IS NULL 和 IS NOT NULL 来检查 NULL 值，而不是使用等号：

SELECT * FROM example_table WHERE description IS NULL;
SELECT * FROM example_table WHERE description IS NOT NULL;

1.4 设置默认值

设置列的默认值可以确保在插入数据时未指定列值时，列会自动使用默认值。

示例：

CREATE TABLE example_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    description VARCHAR(100) NOT NULL DEFAULT 'No Description'
);

1.5 修改表结构

在实际应用中，可能需要修改表结构以适应业务需求的变化。可以使用 ALTER TABLE 语句来添加、删除或修改列。

添加列：

ALTER TABLE example_table ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

删除列：

ALTER TABLE example_table DROP COLUMN description;

修改列：

ALTER TABLE example_table MODIFY COLUMN name VARCHAR(100) NOT NULL;

2 实践举例

2.1 处理 NULL 值的方法

1. 在 SQL 查询中处理 NULL 值
2. 在代码中处理 NULL 值

2.1.1 在 SQL 查询中处理 NULL 值

在 SQL 查询中，可以使用 IFNULL 函数来替换 NULL 值。例如，可以将 NULL 值替换为空字符串：

SELECT F_channel, F_channel_error_code, IFNULL(F_channel_error_msg, '') AS F_channel_error_msg, F_inner_error_code, F_interface, F_strategy 
FROM lepay.t_channel_error_code_config;

2.1.2 在代码中处理 NULL 值

在代码中，应该检查 MYSQL_ROW 数组中的值是否为 NULL，而不是检查字符串 “NULL”。

MYSQL_ROW 是 MySQL C API 中定义的类型，通常通过 mysql.h 头文件引入。在 C++ 中处理 MySQL 数据库，可以使用 MySQL 提供的 C API 或者使用更高级的封装库如 mysql++。

例如，在 C++ 中，可以手动检查 NULL 值。

std::string channelErrorMsg = (row[2] != nullptr) ? row[2] : "";  // 处理 NULL 值

References

• https://dev.mysql.com/doc/
• https://dev.mysql.com/doc/refman/8.0/en/data-types.html
• https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html