MySQL命令2

索引与外键

// 添加索引
ALTER TABLE orders ADD KEY order_ix_custid(cust_id);
// 删除索引
ALTER TABLE orders DROP KEY order_ix_custid;
// 添加外键
ALTER TABLE orderitem ADD CONSTRAINT fk_orderitem_orders FOREIGN KEY (order_id) REFERENCES orders (id);
// 添加外键，支持级联删除
ALTER TABLE orderitem ADD CONSTRAINT fk_orderitem_orders FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE;
// 删除外键
ALTER TABLE orderitem DROP FOREIGN KEY fk_orderitem_orders;
// 添加唯一索引
ALTER TABLE orderitem ADD UNIQUE KEY uc_orderitem(order_id, product_id);

order是MySQL的一个关键字，不要用来做表名，否则有坑。

触发器

// 创建AFTER INSERT触发器
DELIMITER #
CREATE TRIGGER trigger_new_product AFTER INSERT ON wishitem FOR EACH ROW 
BEGIN 
DECLARE new_url VARCHAR(128);
SELECT thumbnail_url FROM product WHERE id = NEW.product_id INTO new_url;
UPDATE wish SET thumbnail_url = new_url WHERE id = NEW.wish_id;
END #
DELIMITER ;

// 创建AFTER DELETE触发器
DELIMITER #
CREATE TRIGGER trigger_del_product AFTER DELETE ON wishitem FOR EACH ROW 
BEGIN 
DECLARE prod_url VARCHAR(128);
DECLARE prod_time DATETIME;
SELECT cp.thumbnail_url, wi.add_time FROM wishitem AS wi INNER JOIN product AS cp on wi.product_id =
cp.id WHERE wi.wish_id = OLD.wish_id ORDER BY wi.add_time LIMIT 1 INTO prod_url, prod_time;
IF prod_url IS NULL THEN
  SET prod_url = '0';
END IF;
UPDATE wish SET thumbnail_url = prod_url WHERE id = OLD.wish_id;
END #
DELIMITER ;

// 显示触发器
SHOW TRIGGERS;

// 删除触发器
DROP TRIGGER trigger_new_product;

replace into

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

INSERT ... ON DUPLICATE KEY UPDATE

If you specify an ON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in a UNIQUEindex or PRIMARY KEY, an UPDATE of the old row occurs.

REPLACE INTO是先删除再插入，如果主键是AUTO_INCREMENT，会导致主键自增。

现有一个表wishitems

可以使用 SHOW CREATE TABLE wishitem 查看表的AUTO_INCREMENT值

CREATE TABLE wishitem (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  wish_id bigint(20) unsigned NOT NULL DEFAULT '0',
  product_id bigint(20) unsigned NOT NULL DEFAULT '0',
  add_time datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  PRIMARY KEY (id),
  UNIQUE KEY uc_wishitem (wish_id, product_id),
  CONSTRAINT fk_wishitem_wish FOREIGN KEY (wish_id) REFERENCES wish (id) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=15000000000000000038 DEFAULT CHARSET=utf8

现在执行

REPLACE INTO wishitem(wish_id, product_id) VALUES(14000000000000000009,99999100000000037)

再次查看表中数据

再次使用 SHOW CREATE TABLE wishitem 查看表的AUTO_INCREMENT值

CREATE TABLE wishitem (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  wish_id bigint(20) unsigned NOT NULL DEFAULT '0',
  product_id bigint(20) unsigned NOT NULL DEFAULT '0',
  add_time datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  PRIMARY KEY (id),
  UNIQUE KEY uc_wishitem (wish_id, product_id),
  CONSTRAINT fk_wishitem_wish FOREIGN KEY (wish_id) REFERENCES wish (id) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=15000000000000000039 DEFAULT CHARSET=utf8

果然，主键ID自增了。

INSERT ... ON DUPLICATE KEY UPDATE不会出现这个问题。

AUTO_INCREMENT

对应表下一个要插入的id

You can retrieve the most recent automatically generated AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function

# 重新设置AUTO_INCREMENT
ALTER TABLE tablename AUTO_INCREMENT = 1

参考资料:

Insert into a MySQL table or update if exists

truncate foreign key constrained table

How to reset AUTO_INCREMENT in MySQL?