MySQL - 一个困难的 INSERT...SELECT 在同一个表上 [MySQL 5.051] [英] MySQL - A difficult INSERT...SELECT on the same table [MySQL 5.051]

问题描述

我试图在我的表中插入一个新行，该行与我试图从同一个表中选择的数据相同，但具有不同的 user_id 并且没有固定的 auto_id 因为这是一个 auto_increment 字段，并将 ti 设置为 NOW().下面是我的模型查询，其中1"是新的 user_id.我一直在尝试许多变体，但仍然卡住了，任何可以帮助我将其转换为有效查询的人.

I am trying to insert a new row into my table which holds the same data as the one I am trying to select from the same table but with a different user_id and without a fixed value for auto_id since that is an auto_increment field, and setting ti to NOW(). Below is my mockup query where '1' is the new user_id. I have been trying many variations but am still stuck, anyone who can help me with turning this into a working query.

INSERT INTO `lins` ( `user_id` , `ad` , `ke` , `se` , `la` , `ra` , `ty` , `en` , `si` , `mo` , `ti` , `de` , `re` , `ti` ) (

SELECT '1', `ad` , `ke` , `se` , `la` , `ra` , `ty` , `en` , `si` , `mo` , `ti` , `de` , `re` , NOW( )
FROM `lins`
WHERE autoid = '4'
AND user_id = '2'
)

感谢您抽出时间帮助我！

Thank you for taking the time to help me out!

推荐答案

想必您知道WHERE autoid = 4"如果是唯一的 autoid 就足够了.如果您打算用单引号引用整数(1、4 和 2)，并且它们是数字，那么您已经创建了隐式转换；在 WHERE 子句中，这将禁用优化器对结果整数值使用索引的能力.

Presumably you know that "WHERE autoid = 4" is sufficient if it's a unique autoid. And if you intended to single-quote the integers (1, 4 and 2), and they're numeric, you've created implied casts; in the WHERE clause, that will disable the ability of the optimizer to use indexes on the resulting integer values.

此外，使用不必要的(并且很容易打错)反引号是此处至少另一个类似问题的根源.

Also, using unnecessary (and easily mistyped) back-ticks has been the root of at least one other similar question here.

这篇关于MySQL - 一个困难的 INSERT...SELECT 在同一个表上 [MySQL 5.051]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！