板凳-------Mysql cookbook学习 （十二--------2)

第10章 元数据 354
10.0 概述 354
10.1 获取受语句影响的行数 356
10.2 获取结果集的元数据 359
10.3 确认语句是否会生成结果集 369
10.4 使用元数据来格式化查询输出 369
10.5 查看数据库和表或检查其存在性 373

mysql> update profile set cats = 0 where cats = 0;
Query OK, 0 rows affected (0.03 sec)
Rows matched: 2  Changed: 0  Warnings: 0mysql> select id, name, birth from profile;
+----+---------+------------+
| id | name    | birth      |
+----+---------+------------+
|  1 | Fred    | 1970-04-13 |
|  2 | Mort    | 1969-09-30 |
|  3 | Brit    | 1957-12-01 |
|  4 | Carl    | 1973-11-02 |
|  5 | Sean    | 1963-07-04 |
|  6 | Alan    | 1965-02-14 |
|  7 | Mara    | 1968-09-17 |
|  8 | Shepard | 1975-09-02 |
|  9 | Dick    | 1952-08-20 |
| 10 | Tony    | 1960-05-01 |
| 11 | Alison  | 1973-01-12 |
| 12 | De'Mont | 1973-01-12 |
| 13 | De'Mont | 1973-01-12 |
| 14 | De'Mont | 1973-01-12 |
| 15 | De'Mont | 1973-01-12 |
| 16 | De'Mont | 1973-01-12 |
| 17 | Amabel  | NULL       |
| 18 | De'Mont | 1980-12-12 |
| 19 | Juan    | NULL       |
| 20 | De'Mont | 1980-12-12 |
+----+---------+------------+
20 rows in set (0.00 sec)mysql> select schema_name from information_schema.schemata;
+--------------------+
| SCHEMA_NAME        |
+--------------------+
| mysql              |
| information_schema |
| performance_schema |
| sys                |
| cookbook           |
| employees          |
| test_db            |
+--------------------+
7 rows in set (0.02 sec)mysql> select table_name from information_schema.tables-> where table_schema = 'cookbook';
+-----------------------+
| TABLE_NAME            |
+-----------------------+
| actors                |
| adcount               |
。。。。
| weatherdata           |
| weekday               |
+-----------------------+
184 rows in set (0.03 sec)mysql> select table_name from information_schema.tables-> where table_schema = database();
+-----------------------+
| TABLE_NAME            |
+-----------------------+
| actors                |
| adcount               |
。。。。
+-----------------------+
184 rows in set (0.03 sec)

10.6 访问列的定义 375

mysql> describe item;
+--------+----------------------------------------------------------------------+------+-----+---------+----------------+
| Field  | Type                                                                 | Null | Key | Default | Extra          |
+--------+----------------------------------------------------------------------+------+-----+---------+----------------+
| id     | int unsigned                                                         | NO   | PRI | NULL    | auto_increment |
| name   | char(20)                                                             | YES  |     | NULL    |                |
| colors | set('chartreuse','mauve','lime green','puce','hot pink','new_color') | YES  |     | NULL    |                |
+--------+----------------------------------------------------------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)mysql> select * from information_schema.columns-> where table_schema = 'cookbook' and table_name = 'item'-> and column_name = 'colors'\G
*************************** 1. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: cookbookTABLE_NAME: itemCOLUMN_NAME: colorsORDINAL_POSITION: 3COLUMN_DEFAULT: NULLIS_NULLABLE: YESDATA_TYPE: set
CHARACTER_MAXIMUM_LENGTH: 51CHARACTER_OCTET_LENGTH: 204NUMERIC_PRECISION: NULLNUMERIC_SCALE: NULLDATETIME_PRECISION: NULLCHARACTER_SET_NAME: utf8mb4COLLATION_NAME: utf8mb4_0900_ai_ciCOLUMN_TYPE: set('chartreuse','mauve','lime green','puce','hot pink','new_color')COLUMN_KEY:EXTRA:PRIVILEGES: select,insert,update,referencesCOLUMN_COMMENT:GENERATION_EXPRESSION:SRS_ID: NULL
1 row in set (0.00 sec)mysql> select column_name, data_type, is_nullable-> from information_schema.columns-> where table_schema = 'cookbook' and table_name = 'item';
+-------------+-----------+-------------+
| COLUMN_NAME | DATA_TYPE | IS_NULLABLE |
+-------------+-----------+-------------+
| id          | int       | NO          |
| name        | char      | YES         |
| colors      | set       | YES         |
+-------------+-----------+-------------+
3 rows in set (0.00 sec)mysql> show columns from item like 'colors'\G
*************************** 1. row ***************************Field: colorsType: set('chartreuse','mauve','lime green','puce','hot pink','new_color')Null: YESKey:
Default: NULLExtra:
1 row in set (0.00 sec)mysql> show create table item \G
*************************** 1. row ***************************Table: item
Create Table: CREATE TABLE `item` (`id` int unsigned NOT NULL AUTO_INCREMENT,`name` char(20) DEFAULT NULL,`colors` set('chartreuse','mauve','lime green','puce','hot pink','new_color') DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

10.7 获取ENUM和SET列的信息 380

```python
import pymysql
from pymysql.cursors import DictCursordef get_enumorset_info(connection, db_name, table_name, column_name):"""获取MySQL表中ENUM或SET类型列的详细信息参数:connection: pymysql连接对象db_name: 数据库名table_name: 表名column_name: 列名返回:包含列信息的字典"""try:with connection.cursor(DictCursor) as cursor:# 查询列信息sql = """SELECT COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, CHARACTER_SET_NAME, COLLATION_NAMEFROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_SCHEMA = %sAND TABLE_NAME = %sAND COLUMN_NAME = %s"""cursor.execute(sql, (db_name, table_name, column_name))result = cursor.fetchone()if not result:raise ValueError(f"未找到列 '{column_name}' 在表 '{table_name}' 中")# 解析列类型column_type = result['COLUMN_TYPE'].lower()col_type = Nonevalues = []if column_type.startswith('enum'):col_type = 'ENUM'# 提取ENUM值values_str = column_type[5:-1]  # 移除'enum('和')'values = [v.strip("'") for v in values_str.split("','")]elif column_type.startswith('set'):col_type = 'SET'# 提取SET值values_str = column_type[4:-1]  # 移除'set('和')'values = [v.strip("'") for v in values_str.split("','")]else:col_type = column_type.upper()# 处理可为空性nullable = result['IS_NULLABLE'] == 'YES'# 返回结果字典return {'type': col_type,'values': values if col_type in ['ENUM', 'SET'] else [],'nullable': nullable,'default': result['COLUMN_DEFAULT'],'character_set': result['CHARACTER_SET_NAME'],'collation': result['COLLATION_NAME']}except Exception as e:print(f"获取列信息时出错: {e}")raise# 连接数据库
conn = pymysql.connect(host='localhost',user='cbuser',password='cbpass',database='cookbook'
)# 获取colors列信息（根据实际表结构修改列名）
info = get_enumorset_info(conn, 'cookbook', 'item', 'colors')  # 修改为存在的列名
print(info)conn.close()结果：
{'type': 'SET','values': ['chartreuse', 'mauve', 'lime green', 'puce', 'hot pink', 'new_color'],'nullable': True,'default': None,'character_set': 'utf8mb4','collation': 'utf8mb4_general_ci'
}Thursday, July 17, 2025
设计一个汽车零件采购系统的数据库需要考虑多个方面，包括供应商信息、零件信息、采购订单、库存管理
https://programb.blog.csdn.net/article/details/147889220mysql> -- 创建临时表A
mysql> WITH A AS (->     SELECT 1 AS value UNION ALL->     SELECT 2 UNION ALL->     SELECT 3 UNION ALL->     SELECT 4-> ),->-> -- 创建临时表B-> B AS (->     SELECT 'a' AS letter UNION ALL->     SELECT 'b'-> )->-> -- 执行CROSS JOIN-> SELECT A.value, B.letter-> FROM A-> CROSS JOIN B-> ORDER BY A.value, B.letter;
+-------+--------+
| value | letter |
+-------+--------+
|     1 | a      |
|     1 | b      |
|     2 | a      |
|     2 | b      |
|     3 | a      |
|     3 | b      |
|     4 | a      |
|     4 | b      |
+-------+--------+
8 rows in set (0.00 sec)itertools --- 为高效循环创建迭代器的函数 — Python 3.12.10 文档
import itertools
import operator# 示例1：itertools.product
print(list(itertools.product([1, 2], ['a', 'b'])))# 示例2：accumulate函数
def accumulate(iterable, function=operator.add, *, initial=None):'Return running totals'iterator = iter(iterable)total = initialif initial is None:try:total = next(iterator)except StopIteration:returnyield totalfor element in iterator:total = function(total, element)yield total# 测试accumulate函数
print(list(accumulate([1,2,3,4,5])))  # 输出: [1, 3, 6, 10, 15]
print(list(accumulate([1,2,3,4,5], initial=100)))  # 输出: [100, 101, 103, 106, 110, 115]
print(list(accumulate([1,2,3,4,5], operator.mul)))  # 输出: [1, 2, 6, 24, 120][(1, 'a'), (1, 'b'), (2, 'a'), (2, 'b')]
[1, 3, 6, 10, 15]
[100, 101, 103, 106, 110, 115]
[1, 2, 6, 24, 120]

10.8 获取服务器的元数据 382```sql
mysql> select version() ;
+-----------+
| version() |
+-----------+
| 8.0.40    |
+-----------+
1 row in set (0.00 sec)mysql> select database();
+------------+
| database() |
+------------+
| cookbook   |
+------------+
1 row in set (0.00 sec)mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)在 MySQL 中，SHOW STATUS 命令可以带 GLOBAL 或 SESSION 修饰符，但不能用 [ ] 或 | 表示可选参数。正确的语法是：sql
SHOW GLOBAL STATUS;    -- 查看全局状态变量
SHOW SESSION STATUS;   -- 查看当前会话状态变量（SESSION 可以省略）
SHOW STATUS;          -- 默认是 SESSION 状态

10.9 编写适合MySQL服务器版本的应用程序 383

mysql>
mysql> DROP PROCEDURE IF EXISTS expire_password$$
Query OK, 0 rows affected (0.02 sec)mysql>
mysql> CREATE PROCEDURE expire_password(IN p_user TEXT, IN p_host TEXT)-> BEGIN->     DECLARE v_account TEXT;->->     -- 安全拼接用户名和主机名（处理特殊字符）->     SET v_account = CONCAT('`', REPLACE(p_user, '`', '``'), '`@`', REPLACE(p_host, '`', '``'), '`');->->     -- 调试信息（可选）->     SELECT CONCAT('Executing: ALTER USER ', v_account, ' PASSWORD EXPIRE') AS debug;->->     -- 直接执行（不再检查存在性，因为手动执行已确认可以工作）->     SET @sql = CONCAT('ALTER USER ', v_account, ' PASSWORD EXPIRE');->     PREPARE stmt FROM @sql;->     EXECUTE stmt;->     DEALLOCATE PREPARE stmt;->->     SELECT CONCAT('Success: Password expired for ', v_account) AS result;-> END$$
Query OK, 0 rows affected (0.01 sec)mysql>
mysql> DELIMITER ;
mysql> -- 标准调用（注意host值必须精确匹配）
mysql> CALL expire_password('cbuser', 'localhost');
+------------------------------------------------------------+
| debug                                                      |
+------------------------------------------------------------+
| Executing: ALTER USER `cbuser`@`localhost` PASSWORD EXPIRE |
+------------------------------------------------------------+
1 row in set (0.00 sec)+----------------------------------------------------+
| result                                             |
+----------------------------------------------------+
| Success: Password expired for `cbuser`@`localhost` |
+----------------------------------------------------+
1 row in set (0.03 sec)Query OK, 0 rows affected (0.05 sec)mysql> CALL expire_password('cbuser', 'root');
+-------------------------------------------------------+
| debug                                                 |
+-------------------------------------------------------+
| Executing: ALTER USER `cbuser`@`root` PASSWORD EXPIRE |
+-------------------------------------------------------+
1 row in set (0.00 sec)+-----------------------------------------------+
| result                                        |
+-----------------------------------------------+
| Success: Password expired for `cbuser`@`root` |
+-----------------------------------------------+
1 row in set (0.03 sec)Query OK, 0 rows affected (0.04 sec)mysql>
mysql> -- 如果用户名包含特殊字符（如@）
mysql> CALL expire_password('special@user', 'localhost');
+------------------------------------------------------------------+
| debug                                                            |
+------------------------------------------------------------------+
| Executing: ALTER USER `special@user`@`localhost` PASSWORD EXPIRE |
+------------------------------------------------------------------+
1 row in set (0.00 sec)ERROR 1396 (HY000): Operation ALTER USER failed for 'special@user'@'localhost'