板凳-------Mysql cookbook学习 （十一--------12)

第16章：使用存储例程、触发器和事件
16.0 引言

mysql> -- 首先设置分隔符（避免分号被解释为语句结束）
mysql> DELIMITER //
mysql>
mysql> -- 创建第一个存储过程
mysql> CREATE PROCEDURE get_time()-> BEGIN->     SET @current_time = CURTIME();-> END//
ERROR 1304 (42000): PROCEDURE get_time already exists
mysql>
mysql> -- 创建第二个存储过程（调用第一个）
mysql> CREATE PROCEDURE part_of_day()-> BEGIN->     CALL get_time();->->     -- 这里可以添加更多逻辑，例如：->     IF HOUR(@current_time) < 12 THEN->         SELECT 'Morning' AS part_of_day;->     ELSEIF HOUR(@current_time) < 18 THEN->         SELECT 'Afternoon' AS part_of_day;->     ELSE->         SELECT 'Evening' AS part_of_day;->     END IF;-> END//
Query OK, 0 rows affected (0.01 sec)mysql>
mysql> -- 恢复默认分隔符
mysql> DELIMITER ;
mysql>
mysql> -- 测试调用
mysql> CALL part_of_day();
+-------------+
| part_of_day |
+-------------+
| Evening     |
+-------------+
1 row in set (0.01 sec)Query OK, 0 rows affected (0.02 sec)
关键点说明：
1.	DELIMITER 命令：临时将语句分隔符从 ; 改为 //，这样存储过程中的分号不会提前终止创建过程
2.	BEGIN/END 块：每个存储过程体必须用 BEGIN 和 END 包围
3.	调用顺序：确保被调用的存储过程（get_time()）已经存在
4.	完整语法：存储过程可以包含复杂的逻辑（如我的示例中添加了时间段判断）

16.1 创建复合语句对象

mysql> -- 首先设置分隔符
mysql> DELIMITER //
mysql>
mysql> -- 创建第一个无参数函数（已成功）
mysql> CREATE FUNCTION avg_mail_size()-> RETURNS FLOAT READS SQL DATA-> RETURN (SELECT AVG(size) FROM mail)//
ERROR 1304 (42000): FUNCTION avg_mail_size already exists
mysql>
mysql> -- 创建带参数的函数
mysql> CREATE FUNCTION avg_mail_size(user VARCHAR(8))-> RETURNS FLOAT READS SQL DATA-> BEGIN->     IF user IS NULL THEN->         RETURN (SELECT AVG(size) FROM mail);->     ELSE->         RETURN (SELECT AVG(size) FROM mail WHERE srcuser = user);->     END IF;-> END//
ERROR 1304 (42000): FUNCTION avg_mail_size already exists
mysql>
mysql> -- 恢复默认分隔符
mysql> DELIMITER ;
mysql>mysql> DROP FUNCTION IF EXISTS avg_mail_size;
Query OK, 0 rows affected (0.02 sec)mysql> DELIMITER $$
mysql> CREATE FUNCTION avg_mail_size(user VARCHAR(8))-> RETURNS FLOAT READS SQL DATA-> BEGIN->     IF user IS NULL THEN->         RETURN (SELECT AVG(size) FROM mail);->     ELSE->         RETURN (SELECT AVG(size) FROM mail WHERE srcuser = user);->     END IF;-> END$$
Query OK, 0 rows affected (0.02 sec)mysql> DELIMITER ;
mysql> select avg_mail_size(null), avg_mail_size('barb');
+---------------------+-----------------------+
| avg_mail_size(null) | avg_mail_size('barb') |
+---------------------+-----------------------+
|              237387 |                 52232 |
+---------------------+-----------------------+
1 row in set (0.01 sec)

16.2 使用存储函数封装计算

mysql> DELIMITER $$
mysql>
mysql> CREATE FUNCTION sales_tax(state_param CHAR(2), amount_param DECIMAL(10, 2))-> RETURNS DECIMAL(10, 2) READS SQL DATA-> BEGIN->     DECLARE rate_var DECIMAL(3, 2);  -- 修正声明语法->     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET rate_var = 0;->->     SELECT tax_rate INTO rate_var->     FROM sales_tax_rate->     WHERE state = state_param;->->     RETURN amount_param * rate_var;-> END$$
Query OK, 0 rows affected (0.01 sec)mysql>
mysql> DELIMITER ;mysql> select sales_tax('ZZ', 100.00);
+-------------------------+
| sales_tax('ZZ', 100.00) |
+-------------------------+
|                    0.00 |
+-------------------------+
1 row in set (0.01 sec)完整测试流程示例mysql> -- 1. 确保表结构正确
mysql> CREATE TABLE IF NOT EXISTS sales_tax_rate (->     state CHAR(2) PRIMARY KEY,->     tax_rate DECIMAL(3,2)-> );
Query OK, 0 rows affected, 1 warning (0.01 sec)mysql>
mysql> -- 2. 清空并重置测试数据
mysql> TRUNCATE TABLE sales_tax_rate;
Query OK, 0 rows affected (0.07 sec)mysql> INSERT INTO sales_tax_rate VALUES->     ('CA', 0.08),->     ('NY', 0.09),->     ('TX', 0.0625);
Query OK, 3 rows affected, 1 warning (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 1mysql>
mysql> -- 3. 重新创建函数（确保使用最新版本）
mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS sales_tax$$
Query OK, 0 rows affected (0.01 sec)mysql> CREATE FUNCTION sales_tax(state_param CHAR(2), amount_param DECIMAL(10,2))-> RETURNS DECIMAL(10,2) READS SQL DATA-> BEGIN->     DECLARE rate_var DECIMAL(3,2) DEFAULT 0;->     DECLARE CONTINUE HANDLER FOR NOT FOUND SET rate_var = 0;->->     SELECT tax_rate INTO rate_var->     FROM sales_tax_rate->     WHERE state = state_param;->->     RETURN amount_param * rate_var;-> END$$
Query OK, 0 rows affected (0.02 sec)mysql> DELIMITER ;
mysql>
mysql> -- 4. 全面测试
mysql> SELECT->     sales_tax('CA', 100) AS California,->     sales_tax('NY', 100) AS NewYork,->     sales_tax('TX', 100) AS Texas,->     sales_tax('XX', 100) AS UnknownState;
+------------+---------+-------+--------------+
| California | NewYork | Texas | UnknownState |
+------------+---------+-------+--------------+
|       8.00 |    9.00 |  6.00 |         0.00 |
+------------+---------+-------+--------------+
1 row in set (0.00 sec)

16.3 使用存储过程来“返回”多个值

mysql> DELIMITER $$
mysql>
mysql> CREATE PROCEDURE mail_sends_stats(->     IN user VARCHAR(8),->     OUT messages INT,->     OUT total_size FLOAT,->     OUT avg_size FLOAT-> )-> BEGIN->     SELECT COUNT(*), IFNULL(SUM(size), 0), IFNULL(AVG(size), 0)->     INTO messages, total_size, avg_size->     FROM mail WHERE srcuser = user;-> END$$
Query OK, 0 rows affected (0.01 sec)mysql>
mysql> DELIMITER ;mysql> call mail_sends_stats('barb', @messages, @total_size, @avg_size);
Query OK, 1 row affected (0.00 sec)mysql> select @messages, @total_size, @avg_size;
+-----------+-------------+-----------+
| @messages | @total_size | @avg_size |
+-----------+-------------+-----------+
|         3 |      156696 |     52232 |
+-----------+-------------+-----------+
1 row in set (0.00 sec)
16.4 用触发器来定义动态的默认列值
mysql> create table doc_table(-> author varchar(100) not null,-> title  varchar(100) not null,-> documer mediumblob not null,-> doc_hash char(32) not null,-> primary key (doc_hash)-> );
Query OK, 0 rows affected (0.05 sec)mysql> create trigger bi_doc_table before insert on doc_table-> for each row set new.doc_hash = MD5(new.documer);
Query OK, 0 rows affected (0.01 sec)mysql> insert into doc_table (author, title, documer)-> values('Mr. Famous Writer', 'My life as a writer', 'This is the document');
Query OK, 1 row affected (0.01 sec)mysql> SELECT * FROM doc_table\G
*************************** 1. row ***************************author: Mr. Famous Writertitle: My life as a writerdocumer: 0x546869732069732074686520646F63756D656E74
doc_hash: 5282317909724f9f1e65318be129539c
1 row in set (0.00 sec)mysql> CREATE TRIGGER bu_doc_table-> BEFORE UPDATE ON doc_table
1.	    -> FOR EACH ROW  #确保 FOR EACH ROW 作为完整的语法单元-> SET NEW.doc_hash = MD5(NEW.documer);
Query OK, 0 rows affected (0.02 sec)mysql> -- 删除已存在的同名触发器（如果存在）
mysql> DROP TRIGGER IF EXISTS bu_doc_table;
Query OK, 0 rows affected (0.01 sec)mysql>
mysql> -- 创建更新触发器
mysql> CREATE TRIGGER bu_doc_table-> BEFORE UPDATE ON doc_table-> FOR EACH ROW-> SET NEW.doc_hash = MD5(NEW.documer);
Query OK, 0 rows affected (0.01 sec)mysql>
mysql> -- 测试触发器
mysql> UPDATE doc_table-> SET documer = 'Updated document content'-> WHERE author = 'Mr. Famous Writer';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select MD5('a new document');
+----------------------------------+
| MD5('a new document')            |
+----------------------------------+
| a5136f98d2313cc850527957b4293f60 |
+----------------------------------+
1 row in set (0.00 sec)

16.5 为其他日期和时间类型模拟timestamp属性

mysql> create table ts_emulate(-> data char(10),-> d    date,-> t    time,-> dt    datetime-> );
Query OK, 0 rows affected (0.04 sec)mysql> create trigger bi_ts_emulate before insert on ts_emulate-> for each row set new.d = curdate(), new.t = curtime(), new.dt = now();
Query OK, 0 rows affected (0.01 sec)mysql> insert into ts_emulate (data) values ('cat');
Query OK, 1 row affected (0.01 sec)mysql> insert into ts_emulate (data) values ('dog');
Query OK, 1 row affected (0.01 sec)mysql> select * from ts_emulate;
+------+------------+----------+---------------------+
| data | d          | t        | dt                  |
+------+------------+----------+---------------------+
| cat  | 2025-07-15 | 21:40:48 | 2025-07-15 21:40:48 |
| dog  | 2025-07-15 | 21:40:59 | 2025-07-15 21:40:59 |
+------+------------+----------+---------------------+
2 rows in set (0.00 sec)mysql> update ts_emulate set data = 'axolot1' where data = 'cat';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from ts_emulate;
+---------+------------+----------+---------------------+
| data    | d          | t        | dt                  |
+---------+------------+----------+---------------------+
| axolot1 | 2025-07-15 | 21:40:48 | 2025-07-15 21:40:48 |
| dog     | 2025-07-15 | 21:40:59 | 2025-07-15 21:40:59 |
+---------+------------+----------+---------------------+
2 rows in set (0.00 sec)mysql> update ts_emulate set data = data;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 0mysql> select * from ts_emulate;
+---------+------------+----------+---------------------+
| data    | d          | t        | dt                  |
+---------+------------+----------+---------------------+
| axolot1 | 2025-07-15 | 21:40:48 | 2025-07-15 21:40:48 |
| dog     | 2025-07-15 | 21:40:59 | 2025-07-15 21:40:59 |
+---------+------------+----------+---------------------+
2 rows in set (0.00 sec)

16.6 使用触发器记录表的变化

拍卖流程图 java 拍卖系统开发_mob6454cc780924的技术博客_51CTO博客drop database if exists auction;create database auction;use auction;#用户表
create table auction_user(user_id int(11) auto_increment,username varchar(50) not null,userpass varchar(50) not null,email varchar(100) not null,primary key(user_id),unique(username)
);INSERT INTO auction_user (username,userpass,email) VALUES ('tomcat','tomcat','spring_test@');
INSERT INTO auction_user (username,userpass,email) VALUES ('mysql','mysql','spring_test@');#物品种类表
create table kind(kind_id int(11) auto_increment,kind_name varchar(50) not null, kind_desc varchar(255) not null,primary key(kind_id)
);INSERT INTO kind (kind_name,kind_desc) VALUES ('电脑硬件','这里并不是很主流的产品，但价格绝对令你心动');
INSERT INTO kind (kind_name,kind_desc) VALUES ('房产','提供非常稀缺的房源');#物品状态表
create table state(state_id int(11) auto_increment,state_name varchar(10),primary key(state_id)
);INSERT INTO state (state_name) VALUES ('拍卖中');
INSERT INTO state (state_name) VALUES ('拍卖成功');
INSERT INTO state (state_name) VALUES ('流拍');#物品表
create table item(item_id int(11) auto_increment,item_name varchar(255) not null,item_remark varchar(255),item_desc varchar(255),kind_id int(11) not null, addtime date not null,endtime date not null,init_price double not null, max_price double not null, owner_id int(11) not null, winer_id int(11), state_id int(11) not null, primary key(item_id),FOREIGN KEY(kind_id) REFERENCES kind(kind_id), FOREIGN KEY(owner_id) REFERENCES auction_user(user_id),FOREIGN KEY(winer_id) REFERENCES auction_user(user_id),FOREIGN KEY(state_id) REFERENCES state(state_id)
); # 拍卖中的物品
INSERT INTO item ( item_name , item_remark , item_desc, kind_id, addtime , endtime, init_price,  max_price,  owner_id,  winer_id,  state_id)VALUES ( '主板', '老式主板', '老主板，还可以用', 1, ADDDATE(CURDATE(), -5), ADDDATE(CURDATE(), 30) , 230, 250, 1,  null,  1);
# 流派的物品
INSERT INTO item ( item_name , item_remark , item_desc, kind_id, addtime , endtime, init_price,  max_price,  owner_id,  winer_id,  state_id)VALUES ( '显卡', '老式显卡', '老显卡，还可以用', 1, ADDDATE(CURDATE(), -9), ADDDATE(CURDATE(), -2), 210, 210, 2,  null,  3);
# 被竞得的物品
INSERT INTO item ( item_name , item_remark , item_desc, kind_id, addtime , endtime, init_price,  max_price,  owner_id,  winer_id,  state_id)VALUES ( '老房子', '老式房子', '40年的老房子', 2, ADDDATE(CURDATE(), -9), ADDDATE(CURDATE(), -5), 21000, 25000, 2,  1,  2);#竞标历史表
create table bid(bid_id int(11) auto_increment,user_id int(11) not null,item_id int(11) not null,bid_price double not null,bid_date date not null, primary key(bid_id),unique(item_id , bid_price),FOREIGN KEY(user_id) REFERENCES auction_user(user_id), FOREIGN KEY(item_id) REFERENCES item(item_id)   
);INSERT INTO bid ( user_id , item_id , bid_price, bid_date)VALUES ( 2, 1, 250, ADDDATE(CURDATE(), -2));
INSERT INTO bid ( user_id , item_id , bid_price, bid_date)VALUES ( 1, 3, 25000, ADDDATE(CURDATE(), -6));Python Web服务器搭建 - 拍卖系统
下面是将Java PO/VO对象和MySQL数据库转换为Python的实现：1. 数据库模型 (PO对象)
python
from datetime import datetime
from typing import List, Set, Optional
from sqlalchemy import Column, Integer, String, Float, Date, ForeignKey, Table
from sqlalchemy.orm import relationship, declarative_baseBase = declarative_base()# 关联表
bid_item_user = Table('bid', Base.metadata,Column('bid_id', Integer, primary_key=True, autoincrement=True),Column('user_id', Integer, ForeignKey('auction_user.user_id'), nullable=False),Column('item_id', Integer, ForeignKey('item.item_id'), nullable=False),Column('bid_price', Float, nullable=False),Column('bid_date', Date, nullable=False)
)class User(Base):__tablename__ = 'auction_user'user_id = Column(Integer, primary_key=True, autoincrement=True)username = Column(String(50), unique=True, nullable=False)userpass = Column(String(50), nullable=False)email = Column(String(100), nullable=False)# 关系owned_items = relationship("Item", back_populates="owner", foreign_keys="Item.owner_id")won_items = relationship("Item", back_populates="winer", foreign_keys="Item.winer_id")bids = relationship("Bid", back_populates="user")class Kind(Base):__tablename__ = 'kind'kind_id = Column(Integer, primary_key=True, autoincrement=True)kind_name = Column(String(50), nullable=False)kind_desc = Column(String(255), nullable=False)items = relationship("Item", back_populates="kind")class State(Base):__tablename__ = 'state'state_id = Column(Integer, primary_key=True, autoincrement=True)state_name = Column(String(10))items = relationship("Item", back_populates="item_state")class Item(Base):__tablename__ = 'item'item_id = Column(Integer, primary_key=True, autoincrement=True)item_name = Column(String(255), nullable=False)item_remark = Column(String(255))item_desc = Column(String(255))# 外键kind_id = Column(Integer, ForeignKey('kind.kind_id'), nullable=False)owner_id = Column(Integer, ForeignKey('auction_user.user_id'), nullable=False)winer_id = Column(Integer, ForeignKey('auction_user.user_id'))state_id = Column(Integer, ForeignKey('state.state_id'), nullable=False)addtime = Column(Date, nullable=False)endtime = Column(Date, nullable=False)init_price = Column(Float, nullable=False)max_price = Column(Float, nullable=False)# 关系kind = relationship("Kind", back_populates="items")owner = relationship("User", back_populates="owned_items", foreign_keys=[owner_id])winer = relationship("User", back_populates="won_items", foreign_keys=[winer_id])item_state = relationship("State", back_populates="items")bids = relationship("Bid", back_populates="item")class Bid(Base):__tablename__ = 'bid'bid_id = Column(Integer, primary_key=True, autoincrement=True)bid_price = Column(Float, nullable=False)bid_date = Column(Date, nullable=False)# 外键user_id = Column(Integer, ForeignKey('auction_user.user_id'), nullable=False)item_id = Column(Integer, ForeignKey('item.item_id'), nullable=False)# 关系user = relationship("User", back_populates="bids")item = relationship("Item", back_populates="bids")
2. 业务对象 (VO对象)
python
from dataclasses import dataclass
from datetime import datetime@dataclass
class ItemBean:id: intname: strdesc: strremark: strkind: strowner: strwiner: strstate: strinit_price: floatmax_price: floatadd_time: datetimeend_time: datetime@dataclass
class BidBean:id: intuser: stritem: strprice: floatbid_date: datetime@dataclass
class KindBean:id: intkind_name: strkind_desc: str
3. 数据库初始化
python
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker# 创建数据库连接
DATABASE_URL = "mysql+pymysql://username:password@localhost/auction"
engine = create_engine(DATABASE_URL)# 创建表
Base.metadata.create_all(engine)# 创建会话工厂
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)def get_db():db = SessionLocal()try:yield dbfinally:db.close()
4. FastAPI 路由示例
python
from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy.orm import Session
from typing import List
from datetime import datetime, timedeltaapp = FastAPI()# 获取所有拍卖物品
@app.get("/items/", response_model=List[ItemBean])
def read_items(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):db_items = db.query(Item).offset(skip).limit(limit).all()items = []for item in db_items:items.append(ItemBean(id=item.item_id,name=item.item_name,desc=item.item_desc,remark=item.item_remark,kind=item.kind.kind_name,owner=item.owner.username,winer=item.winer.username if item.winer else None,state=item.item_state.state_name,init_price=item.init_price,max_price=item.max_price,add_time=item.addtime,end_time=item.endtime))return items# 创建新物品
@app.post("/items/", response_model=ItemBean)
def create_item(item: ItemBean, db: Session = Depends(get_db)):db_item = Item(item_name=item.name,item_desc=item.desc,item_remark=item.remark,kind_id=item.kind_id,addtime=datetime.now(),endtime=datetime.now() + timedelta(days=30),init_price=item.init_price,max_price=item.init_price,owner_id=item.owner_id,state_id=1  # 默认拍卖中)db.add(db_item)db.commit()db.refresh(db_item)return item# 获取单个物品详情
@app.get("/items/{item_id}", response_model=ItemBean)
def read_item(item_id: int, db: Session = Depends(get_db)):db_item = db.query(Item).filter(Item.item_id == item_id).first()if db_item is None:raise HTTPException(status_code=404, detail="Item not found")return ItemBean(id=db_item.item_id,name=db_item.item_name,desc=db_item.item_desc,remark=db_item.item_remark,kind=db_item.kind.kind_name,owner=db_item.owner.username,winer=db_item.winer.username if db_item.winer else None,state=db_item.item_state.state_name,init_price=db_item.init_price,max_price=db_item.max_price,add_time=db_item.addtime,end_time=db_item.endtime)
5. 数据库初始化数据
python
def init_db():db = SessionLocal()try:# 创建测试用户user1 = User(username="tomcat", userpass="tomcat", email="spring_test@")user2 = User(username="mysql", userpass="mysql", email="spring_test@")db.add_all([user1, user2])db.commit()# 创建物品种类kind1 = Kind(kind_name="电脑硬件", kind_desc="这里并不是很主流的产品，但价格绝对令你心动")kind2 = Kind(kind_name="房产", kind_desc="提供非常稀缺的房源")db.add_all([kind1, kind2])db.commit()# 创建物品状态state1 = State(state_name="拍卖中")state2 = State(state_name="拍卖成功")state3 = State(state_name="流拍")db.add_all([state1, state2, state3])db.commit()# 创建物品item1 = Item(item_name="主板",item_remark="老式主板",item_desc="老主板，还可以用",kind_id=kind1.kind_id,addtime=datetime.now() - timedelta(days=5),endtime=datetime.now() + timedelta(days=30),init_price=230,max_price=250,owner_id=user1.user_id,state_id=state1.state_id)item2 = Item(item_name="显卡",item_remark="老式显卡",item_desc="老显卡，还可以用",kind_id=kind1.kind_id,addtime=datetime.now() - timedelta(days=9),endtime=datetime.now() - timedelta(days=2),init_price=210,max_price=210,owner_id=user2.user_id,state_id=state3.state_id)item3 = Item(item_name="老房子",item_remark="老式房子",item_desc="40年的老房子",kind_id=kind2.kind_id,addtime=datetime.now() - timedelta(days=9),endtime=datetime.now() - timedelta(days=5),init_price=21000,max_price=25000,owner_id=user2.user_id,winer_id=user1.user_id,state_id=state2.state_id)db.add_all([item1, item2, item3])db.commit()# 创建竞标记录bid1 = Bid(user_id=user2.user_id,item_id=item1.item_id,bid_price=250,bid_date=datetime.now() - timedelta(days=2))bid2 = Bid(user_id=user1.user_id,item_id=item3.item_id,bid_price=25000,bid_date=datetime.now() - timedelta(days=6))db.add_all([bid1, bid2])db.commit()except Exception as e:db.rollback()raise efinally:db.close()# 调用初始化函数
init_db()
6. 运行FastAPI应用
创建一个main.py文件：python
from fastapi import FastAPI
from models import Base
from database import engine, init_db
import uvicorn# 创建表
Base.metadata.create_all(bind=engine)# 初始化数据
init_db()app = FastAPI()# 导入路由
from routes import itemsapp.include_router(items.router)if __name__ == "__main__":uvicorn.run(app, host="0.0.0.0", port=8000)
7. 依赖安装
创建一个requirements.txt文件：text
fastapi
uvicorn
sqlalchemy
pymysql
python-dotenv
python-dateutil
然后运行：bash
pip install -r requirements.txt
8. 运行应用
bash
python main.py
现在你的拍卖系统Web服务器应该运行在 http://localhost:8000 上，你可以访问 /items/ 端点来获取所有拍卖物品的列表。这个实现使用了:SQLAlchemy 作为ORMFastAPI 作为Web框架Pydantic (通过FastAPI的response_model) 进行数据验证和序列化MySQL 作为数据库你可以根据需要进一步扩展功能，比如添加用户认证、竞价功能、物品搜索等。===================================================
mysql> create table auction(-> id int unsigned not null auto_increment,-> ts  timestamp,-> item varchar(30) not null,-> bid decimal(10, 2) not null,-> primary key(id)-> );
Query OK, 0 rows affected (0.04 sec)mysql> create table auction_log(-> action enum('create', 'update','delete'),-> id int unsigned not null,-> ts timestamp,-> item varchar(30) not null,-> bid decimal(10, 2) not null,-> index  (id)-> );
Query OK, 0 rows affected (0.09 sec)mysql>  DELIMITER ;
mysql> DELIMITER $$
mysql>
mysql> CREATE TRIGGER ai_auction AFTER INSERT ON auction-> FOR EACH ROW-> BEGIN->    INSERT INTO auction_log (action, id, ts, item, bid)->    VALUES('create', NEW.id, NOW(), NEW.item, NEW.bid);-> END$$
Query OK, 0 rows affected (0.02 sec)mysql>
mysql> CREATE TRIGGER au_auction AFTER UPDATE ON auction-> FOR EACH ROW-> BEGIN->    INSERT INTO auction_log (action, id, ts, item, bid)->    VALUES('update', NEW.id, NOW(), NEW.item, NEW.bid);-> END$$
Query OK, 0 rows affected (0.01 sec)mysql>
mysql> CREATE TRIGGER ad_auction AFTER DELETE ON auction-> FOR EACH ROW-> BEGIN->    INSERT INTO auction_log (action, id, ts, item, bid)->    VALUES('delete', OLD.id, OLD.ts, OLD.item, OLD.bid);-> END$$
Query OK, 0 rows affected (0.02 sec)mysql>
mysql> DELIMITER ;
mysql> insert into auction (item, bid) values('chintz pillows', 5.00);
Query OK, 1 row affected (0.01 sec)mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)mysql> -- 1. 首先插入一条记录
mysql> INSERT INTO auction (item, bid) VALUES('chintz pillows', 5.00);
Query OK, 1 row affected (0.01 sec)mysql>
mysql> -- 2. 获取刚插入的ID（假设返回的是1）
mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)mysql>
mysql> -- 3. 对这条记录进行更新（使用正确的ID）
mysql> UPDATE auction SET bid = 7.50 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> UPDATE auction SET bid = 9.00 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> UPDATE auction SET bid = 10.00 WHERE id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql>
mysql> -- 4. 查询日志（使用正确的ID）
mysql> SELECT * FROM auction_log WHERE id = 1 ORDER BY ts;
+--------+----+---------------------+----------------+-------+
| action | id | ts                  | item           | bid   |
+--------+----+---------------------+----------------+-------+
| create |  1 | 2025-07-16 20:33:13 | chintz pillows |  5.00 |
| update |  1 | 2025-07-16 20:37:51 | chintz pillows |  7.50 |
| update |  1 | 2025-07-16 20:37:51 | chintz pillows |  9.00 |
| update |  1 | 2025-07-16 20:37:51 | chintz pillows | 10.00 |
+--------+----+---------------------+----------------+-------+
4 rows in set (0.00 sec)如果您想完全重现书上的示例（ID=792），可以这样做：
-- 1. 首先手动插入ID=792的记录（需要暂时关闭自增）
SET FOREIGN_KEY_CHECKS=0;
INSERT INTO auction (id, item, bid) VALUES(792, 'chintz pillows', 5.00);
SET FOREIGN_KEY_CHECKS=1;-- 2. 然后执行更新操作
UPDATE auction SET bid = 7.50 WHERE id = 792;
UPDATE auction SET bid = 9.00 WHERE id = 792;
UPDATE auction SET bid = 10.00 WHERE id = 792;-- 3. 查询日志
SELECT * FROM auction_log WHERE id = 792 ORDER BY ts;16.7 使用事件调度数据库动作
mysql> describe mark_log ;
+---------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field   | Type         | Null | Key | Default           | Extra                                         |
+---------+--------------+------+-----+-------------------+-----------------------------------------------+
| id      | int          | NO   | PRI | NULL              | auto_increment                                |
| ts      | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| message | varchar(100) | YES  |     | NULL              |                                               |
+---------+--------------+------+-----+-------------------+-----------------------------------------------+
3 rows in set (0.07 sec)mysql> select * from mark_log;
Empty set (0.01 sec)mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set, 1 warning (0.01 sec)mysql> set global event_scheduler = 1;
Query OK, 0 rows affected (0.00 sec)

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若转载，请注明出处：http://www.pswp.cn/diannao/91609.shtml
繁体地址，请注明出处：http://hk.pswp.cn/diannao/91609.shtml

如若内容造成侵权/违法违规/事实不符，请联系多彩编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

linux端口监听命令

linux端口监听命令

端口监听命令&#xff1a; netstat -nlp&#xff5c;grep 86886 netstat -nlp&#xff5c;grep 8686 netstat -nlp&#xff5c;grep 8686 netstat -nl&#xff5c;grep 8686 netstat -n&#xff5c;grep 8686各命令的含义与区别&#xff1a; 1. netstat -nlp | grep 86886 参数…
阅读更多...
APP端定位实现(uniapp Vue3)(腾讯地图)

APP端定位实现(uniapp Vue3)(腾讯地图)

一.申请腾讯地图开发者 官网腾讯位置服务 - 立足生态&#xff0c;连接未来 注册的话可以选择个人和企业,推荐先注册个人,企业的话如果后期有需要到时候个人可以升级为企业 申请完成之后点击控制台进入当前页面后点击应用管理 —— 我的应用 点击创建应用 应用名称随便填(自己知…
阅读更多...
Linux之Zabbix分布式监控篇（二）

Linux之Zabbix分布式监控篇（二）

一、前置回顾zabbix作为一个分布式监控工具&#xff0c;能帮助我们7*24*365监控硬件使用情况&#xff08;cpu,内存&#xff09;&#xff0c;以及网络流量波动&#xff0c;从而能让运维能及时排查错误zabbix由zabbix-server(10051)、zabbix-agent(10050)、zabbix-database、zabb…
阅读更多...
leetcode 3202. 找出有效子序列的最大长度 II 中等

leetcode 3202. 找出有效子序列的最大长度 II 中等

给你一个整数数组 nums 和一个 正 整数 k 。nums 的一个 子序列 sub 的长度为 x &#xff0c;如果其满足以下条件&#xff0c;则称其为 有效子序列 &#xff1a;(sub[0] sub[1]) % k (sub[1] sub[2]) % k ... (sub[x - 2] sub[x - 1]) % k返回 nums 的 最长有效子序列 的…
阅读更多...
Mysql测试题

Mysql测试题

1 Which Linux MySQL server installation directories are the base directories? (Choose two) /usr/sbin /var/lib/mysql /var/log /usr/bin /etc 2 What does the RPM installation process for MySQL do? (Choose two) It creates the default my.cnf file It se…
阅读更多...
自动化测试工具 Selenium 入门指南

自动化测试工具 Selenium 入门指南

Selenium 是一款强大的自动化测试工具&#xff0c;可用于模拟用户在浏览器中的各种操作。它支持多种浏览器&#xff08;如 Chrome、Firefox、Edge 等&#xff09;和多种编程语言&#xff08;如 Python、Java、C# 等&#xff09;&#xff0c;广泛应用于 Web 应用程序的自动化测试…
阅读更多...
Hystrix与Resilience4j在微服务熔断降级中的应用对比与实战

Hystrix与Resilience4j在微服务熔断降级中的应用对比与实战

Hystrix与Resilience4j在微服务熔断降级中的应用对比与实战 1. 问题背景介绍 在微服务架构中&#xff0c;服务之间的依赖使得链路调用更加复杂。一旦某个下游服务发生故障或响应延迟&#xff0c;可能导致整个调用链阻塞甚至雪崩&#xff0c;影响系统可用性。熔断&#xff08;Ci…
阅读更多...
PostgreSQL数据库集群如何进行自动化性能监测？

PostgreSQL数据库集群如何进行自动化性能监测？

前言&#xff1a;在这个数据爆炸的时代&#xff0c;PostgreSQL数据库集群就像是我们的"数据宝库"。但是&#xff0c;再好的宝库也需要有专业的"保安"来守护。今天我们就来聊聊如何给PostgreSQL集群配备一套智能的"保安系统"——自动化性能监测。…
阅读更多...
OneCode体系架构深度剖析：设计哲学与AI增强之道

OneCode体系架构深度剖析：设计哲学与AI增强之道

引言 在企业级应用开发领域&#xff0c;架构设计决定了系统的扩展性、可维护性与演进能力。OneCode作为一站式开发平台&#xff0c;其架构设计蕴含着对复杂业务场景的深刻理解与技术选型的前瞻性思考。本文将从六个维度系统剖析OneCode的架构设计理念&#xff0c;揭示其模块划分…
阅读更多...
AWS中国区资源成本优化全面指南：从理论到实践

AWS中国区资源成本优化全面指南：从理论到实践

引言：为什么AWS中国区成本优化如此重要？ 在数字化转型的浪潮中，越来越多的中国企业选择AWS中国区作为其云计算服务提供商。然而，随着业务规模的扩大，云资源成本往往成为企业关注的焦点。有效的成本优化不仅能够直接降低IT支出，还能提高资源利用效率，为企业创造更大的商…
阅读更多...
Redis中什么是看门狗机制

Redis中什么是看门狗机制

在 Redis 中&#xff0c;“看门狗机制”&#xff08;Watchdog Mechanism&#xff09;不是 Redis 的核心机制之一&#xff0c;但它在一些场景中起到了重要作用&#xff0c;尤其是在使用 Redlock 分布式锁实现 或在 Redis Enterprise 等高级用法中。一、看门狗机制的通用含义看门…
阅读更多...
[MRCTF2020]PYWebsite

[MRCTF2020]PYWebsite

function enc(code){hash hex_md5(code);return hash;}function validate(){var code document.getElementById("vcode").value;if (code ! ""){if(hex_md5(code) "0cd4da0223c0b280829dc3ea458d655c"){alert("您通过了验证&#xff01;…
阅读更多...
AWS S3事件通知实战：从配置到生产的完整指南

AWS S3事件通知实战：从配置到生产的完整指南

引言 在现代云架构中，事件驱动设计已成为构建可扩展、高可用系统的核心模式。AWS S3作为对象存储服务，其事件通知功能为我们提供了强大的自动化处理能力。本文将基于一个真实的图片处理系统案例，详细介绍如何正确配置和使用S3事件通知。 业务场景 我们开发了一个图片处理…
阅读更多...
[AI-video] Web UI | Streamlit(py to web) | 应用配置config.toml

[AI-video] Web UI | Streamlit(py to web) | 应用配置config.toml

链接&#xff1a;https://reccloud.cn/start?positiontab1 docs&#xff1a;AI creates videos MoneyPrinterTurbo 是一个自动化短视频创作流程的开源项目。 它通过输入主题或关键词&#xff0c;利用人工智能&#xff08;大语言模型&#xff09;生成脚本和搜索条件&#xff0…
阅读更多...
CommonJS 功能介绍

CommonJS 功能介绍

CommonJS是JavaScript的模块化规范&#xff0c;主要用于服务器端&#xff08;如Node.js&#xff09;的模块化开发&#xff0c;其核心功能和特点如下&#xff1a; 一、核心功能模块定义与导出 module.exports&#xff1a;用于导出模块的内容&#xff0c;可以是函数、对象、变量等…
阅读更多...
3D材质总监的“光影魔法”：用Substance Sampler AI，“擦除”照片中的光影

3D材质总监的“光影魔法”：用Substance Sampler AI，“擦除”照片中的光影

在三维视觉艺术的创作中&#xff0c;我们始终在探索一对核心的“对立统一”&#xff1a;一方面是**“现实世界的光照”&#xff08;Real-World Lighting&#xff09;&#xff0c;它被固定、“烘焙”在一张照片的像素之中&#xff1b;另一方面是“虚拟世界的光照”&#xff08;V…
阅读更多...
从高斯噪声的角度分析MAE和MSE

从高斯噪声的角度分析MAE和MSE

文章目录1. MAE与MSE的本质区别2. 高斯噪声下的统计特性3. MAE导致稀疏解的内在机制4. 对比总结1. MAE与MSE的本质区别 MAE&#xff08;Mean Absolute Error&#xff09;和MSE&#xff08;Mean Squared Error&#xff09;是两种常用的损失函数&#xff0c;它们的数学形式决定了…
阅读更多...
AR智能巡检：制造业零缺陷安装的“数字监工”

AR智能巡检：制造业零缺陷安装的“数字监工”

在制造业中&#xff0c;设备安装与组装环节的准确性是产品质量和生产效率的关键。传统的人工巡检和纸质作业指导书容易因人为疏忽、经验不足或信息滞后导致安装错误&#xff0c;进而引发返工、延误甚至安全事故。然而&#xff0c;随着增强现实&#xff08;AR www.teamhelper.cn…
阅读更多...
js最简单的解密分析

js最简单的解密分析

js最简单的解密分析 一、JavaScript 代码保护技术简介 ✅ 为什么要保护 JavaScript 代码&#xff1f; JavaScript 是前端语言&#xff0c;代码在浏览器中是完全可见的。这意味着&#xff1a; 别人可以轻松查看你的核心算法或业务逻辑页面上的接口地址、加密逻辑等容易被抓包分析…
阅读更多...
React强大且灵活hooks库——ahooks入门实践之开发调试类hook（dev）详解

React强大且灵活hooks库——ahooks入门实践之开发调试类hook（dev）详解

什么是 ahooks&#xff1f; ahooks 是一个 React Hooks 库&#xff0c;提供了大量实用的自定义 hooks&#xff0c;帮助开发者更高效地构建 React 应用。其中开发调试类 hooks 是 ahooks 的一个重要分类&#xff0c;专门用于开发调试阶段&#xff0c;帮助开发者追踪组件更新和副…
阅读更多...
最新文章

Copyright @ 2022~2023