我在 MySQL 上有一个叫做rent的表
I have a table called rent on MySQL
id, int (5), autoincrement PRIMARY KEY dNo, int (5) pRent, varchar (5) status, varchar (10)我需要在这个表中插入 300 行
I need to insert 300 rows into this table
id 和 d 不需要匹配
id and dNo need to match
所以最后我们将有 id/dNo 直到 300.这怎么做?
so in the end we will have id/dNo up until 300. How can this be done?
pRent 的默认值为 0
pRent will have a default value of 0
status 将有一个默认值 vacant
status will have a default value of vacant
我应该使用什么 SQL 查询一次性插入所有 300 行,id/d 没有自动递增到 300?
What SQL query should I use to insert all 300 rows in at once with id/dNo autoincrement up to 300?
推荐答案假设 id 设置为 auto increment 你可以插入批次然后做一个 update table set dNo=id
Assuming the id is set to auto increment you can just insert the lot then do an update table set dNo=id
我不确定您是否可以在插入期间设置 dNo=id,因为 id 将是 NULL
I'm not sure if you can set dNo=id during insert as the id would be NULL
您可以将 dNo 默认值设置为 id
You might be able to set the dNo default value to id
INSERT INTO `test` (`id`, `id2`) VALUES ('2', `id`)
工作正常,但如果我设置 id=NULL 则不行.
Works fine, but not if I set id=NULL.
看起来您必须使用 触发器
CREATE TRIGGER set_nDo AFTER INSERT ON test FOR EACH ROW SET `nDo`=`id`更多推荐
MySQL查询一次插入300行
发布评论