【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 列将被设置为 NULLmysql> 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 值的方法
- 在 SQL 查询中处理
NULL值 - 在代码中处理
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