IT策士 10余年一线大厂经验专注 IT 思维、架构、职场进阶。我会在各个平台持续发布最新文章助你少走弯路。上一篇文章我们成功用 Python 连接了 MySQL并创建了第一个数据库与表。但你可能会好奇数据库和表在 MySQL 内部是如何组织的建库建表时有哪些重要的配置选项为什么有时候中文字符会变成乱码今天我们就深入“容器”内部掌握 DDL数据定义语言的核心知识。1. 数据库Database数据的顶层容器在 MySQL 中数据库Schema是表的集合同时也承载着权限、字符集等默认配置。可以把数据库理解为 Excel 中的工作簿表就是其中的工作表。1.1 创建数据库的完整语法CREATE DATABASE[IF NOT EXISTS]数据库名[DEFAULT CHARACTER SET 字符集][DEFAULT COLLATE 排序规则];为什么需要IF NOT EXISTS在自动化脚本中重复执行建库语句会导致错误。加上IF NOT EXISTS后如果库已存在则静默跳过不会中断执行。1.2 用 Python 管理数据库下面我们用 Python 来创建、查看、删除数据库完整代码如下importmysql.connector from mysql.connectorimportError def get_connection():创建连接不加 database 参数以便执行建库操作returnmysql.connector.connect(host127.0.0.1,port3306,userroot,passwordMyNewPass123!)try: connget_connection()cursorconn.cursor()# 1. 创建数据库cursor.execute(CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4)print(✅ 数据库 shop 创建成功或已存在)# 2. 查看所有数据库cursor.execute(SHOW DATABASES)databasescursor.fetchall()print(\n 当前数据库列表)fordbindatabases: print(f - {db[0]})# 3. 查看创建语句便于审查cursor.execute(SHOW CREATE DATABASE shop)resultcursor.fetchone()print(f\n shop 建库语句:\n{result[1]})except Error as e: print(f❌ 错误: {e})finally:ifconn.is_connected(): cursor.close()conn.close()运行这段代码预期输出✅ 数据库 shop 创建成功或已存在 当前数据库列表 - information_schema - myblog - mysql - performance_schema - shop - sys shop 建库语句: CREATE DATABASEshop/*!40100DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */可以看到 MySQL 自动为我们选用了默认的排序规则utf8mb4_0900_ai_ci。1.3 修改与删除数据库# 修改数据库的字符集cursor.execute(ALTER DATABASE shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin)cursor.execute(SHOW CREATE DATABASE shop)print(cursor.fetchone()[1])# 删除数据库谨慎生产环境禁用此操作# cursor.execute(DROP DATABASE IF EXISTS shop)最佳实践数据库名称应遵循小写下划线命名避免使用保留字如order、user。名称一经确定尽量不要改动因为涉及大量代码调整。2. 表Table数据的真正载体表是数据的直接存放地。定义一张表就是定义它包含哪些列以及每列的数据类型和约束。2.1 建表的基本语法CREATE TABLE[IF NOT EXISTS]表名(列名1 数据类型[约束], 列名2 数据类型[约束],...[表级约束])[ENGINE引擎][DEFAULTCHARSET字符集];2.2 创建一张标准的商品表我们结合电商场景用 Python 创建一张products表importmysql.connector connmysql.connector.connect(host127.0.0.1,port3306,userroot,passwordMyNewPass123!,databaseshop)cursorconn.cursor()create_table_sql CREATE TABLE IF NOT EXISTS products(idINT AUTO_INCREMENT, title VARCHAR(200)NOT NULL, price DECIMAL(10,2)NOT NULL, stock INT DEFAULT0, category VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(id), UNIQUE KEY uk_title(title))ENGINEInnoDB DEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci cursor.execute(create_table_sql)print(✅ 表 products 创建成功)# 查看表结构cursor.execute(DESCRIBE products)print(\n products 表结构)print(f{Field:15} {Type:20} {Null:8} {Key:8} {Default:15} {Extra:20})forrowincursor.fetchall(): print(f{row[0]:15} {row[1]:20} {row[2]:8} {row[3]:8} {str(row[4]):15} {row[5]:20})cursor.close()conn.close()预期输出✅ 表 products 创建成功 products 表结构 Field Type Null Key Default Extraidint NO PRI None auto_increment title varchar(200)NO UNI None price decimal(10,2)NO None stock int YES0category varchar(50)YES None created_at timestamp YES CURRENT_TIMESTAMP DEFAULT_GENERATED3. 约束Constraints数据完整性的守护神约束就像是数据的“安检门”不满足条件的数据将被拒之门外。以下是 MySQL 中最常用的约束。3.1 PRIMARY KEY主键主键 非空 唯一 一张表只能有一个-- 单列主键idINT PRIMARY KEY AUTO_INCREMENT -- 复合主键多列共同唯一 PRIMARY KEY(user_id, role_id)为什么推荐 AUTO_INCREMENT自增主键能保证插入有序对 InnoDB 的 BTree 索引非常友好可减少页分裂提升写入性能。3.2 UNIQUE唯一约束确保一列或多列的组合值不重复但允许 NULL多个 NULL 不算重复。# 测试唯一约束try: cursor.execute(INSERT INTO products (title, price) VALUES (iPhone 15, 6999.00))conn.commit()print(✅ 插入成功)# 再次插入相同 titlecursor.execute(INSERT INTO products (title, price) VALUES (iPhone 15, 6999.00))conn.commit()except mysql.connector.IntegrityError as e: print(f❌ 违反唯一约束: {e})预期输出✅ 插入成功 ❌ 违反唯一约束:1062(23000): Duplicate entryiPhone 15forkeyproducts.uk_title3.3 NOT NULL非空约束列值不能为 NULL。对于重要字段如价格、标题必须施加此约束。3.4 DEFAULT默认值插入时不指定该列则自动填充默认值。上面stock默认 0created_at默认当前时间戳。3.5 CHECK检查约束MySQL 8.0.16限制列的取值范围比如价格必须大于 0price DECIMAL(10,2)NOT NULL CHECK(price0)3.6 FOREIGN KEY外键用于关联两张表确保子表引用的数据在父表存在。但互联网高并发场景下很少使用数据库级外键而是由应用层保证逻辑一致性避免死锁、性能损耗。-- 仅做了解 CREATE TABLE orders(idINT PRIMARY KEY AUTO_INCREMENT, product_id INT, FOREIGN KEY(product_id)REFERENCES products(id));常见误区很多教程强调外键的重要性但在大厂实践中外键通常被禁用。原因是它会引入额外的锁和级联操作影响性能。数据一致性交由 Service 层或分布式事务保障。4. 字符集与排序规则彻底解决乱码4.1 你遇到的乱码大概率是字符集在捣鬼当存储 emoji 表情如时如果使用utf8最多 3 字节就会报错或出现?。MySQL 8.0 默认的utf8mb4支持 4 字节 Unicode是真正意义上的 UTF-8。4.2 常用字符集对比4.3 排序规则Collation的影响排序规则决定了字符串如何比较和排序命名规则是字符集_语言_大小写敏感性。utf8mb4_general_ci通用、快速但不严谨旧默认utf8mb4_unicode_ci更准确基于 Unicode 标准utf8mb4_0900_ai_ciMySQL 8.0 新默认基于 Unicode 9.0AIAccent Insensitiveutf8mb4_bin按二进制值比较大小写敏感如a≠A# 演示排序规则区别cursor.execute(SELECT a A COLLATE utf8mb4_0900_ai_ci)# 1相等print(大小写不敏感:, cursor.fetchone()[0])cursor.execute(SELECT a A COLLATE utf8mb4_bin)# 0不相等print(大小写敏感:, cursor.fetchone()[0])预期输出4.4 字符集配置建议最佳实践在创建数据库时就指定好避免后续迁移痛苦CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;建表时也可以覆盖数据库的默认设置。确保 Python 连接也指定charsetutf8mb4connmysql.connector.connect(...charsetutf8mb4,collationutf8mb4_unicode_ci)5. 修改表结构拥抱变化业务需求变化时需要修改表结构。MySQL 通过ALTER TABLE支持添加列ALTER TABLE products ADD COLUMN description TEXT AFTER category;修改列类型ALTER TABLE products MODIFY COLUMN price DECIMAL(12,2);删除列ALTER TABLE products DROP COLUMN description;添加索引ALTER TABLE products ADD INDEX idx_category (category);在线 DDL 的风险警示ALTER TABLE 可能会锁表对于千万级大表直接修改结构会导致长时间业务不可用。MySQL 8.0 对许多操作支持 In-place 算法不会全表复制但仍有风险。大表变更推荐使用pt-online-schema-change工具本系列后续会详细介绍。6. 动手试试设计你自己的数据表假设你要为博客系统设计两张表文章表articles和评论表comments。要求如下articles包含自增主键、标题唯一非空、正文、作者名、发布时间默认当前时间、状态0草稿/1已发布默认为0。comments包含自增主键、关联文章 ID、评论人昵称、评论内容、创建时间。所有字符串使用utf8mb4引擎使用 InnoDB。请先自己尝试写出建表 SQL再用 Python 执行并打印SHOW CREATE TABLE的结果。参考代码connmysql.connector.connect(host127.0.0.1,port3306,userroot,passwordMyNewPass123!,databaseblog)cursorconn.cursor()# 建库cursor.execute(CREATE DATABASE IF NOT EXISTS blog DEFAULT CHARSET utf8mb4)cursor.execute(USE blog)# 建文章表cursor.execute( CREATE TABLE IF NOT EXISTS articles(idINT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200)NOT NULL UNIQUE, content TEXT NOT NULL, author VARCHAR(50)NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, status TINYINT DEFAULT0COMMENT0草稿1已发布)ENGINEInnoDB DEFAULTCHARSETutf8mb4)# 建评论表cursor.execute( CREATE TABLE IF NOT EXISTS comments(idINT AUTO_INCREMENT PRIMARY KEY, article_id INT NOT NULL, nickname VARCHAR(50)NOT NULL, body TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)ENGINEInnoDB DEFAULTCHARSETutf8mb4)# 打印建表语句cursor.execute(SHOW CREATE TABLE articles)print(cursor.fetchone()[1])cursor.execute(SHOW CREATE TABLE comments)print(cursor.fetchone()[1])cursor.close()conn.close()7. 总结与下篇预告今天我们掌握了 MySQL 中两个核心容器——数据库和表的创建与管理学习了主键、唯一、非空、默认值等约束的实际用法并彻底弄懂了字符集与排序规则。这些知识是后续所有操作的地基。下篇文章将深入数据类型全解我们一起探究 VARCHAR 与 CHAR 的真相、如何选择最适合的数值与日期类型以及 JSON 列的最佳实践。敬请期待想了解更多还可以去各个平台搜索「IT策士」一起升级 IT 思维