索引与外键

// 添加索引

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 = '';

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 '',

  product_id bigint(20) unsigned NOT NULL DEFAULT '',

  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 '',

  product_id bigint(20) unsigned NOT NULL DEFAULT '',

  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?

巴特西

MySQL命令2