mysql - 使用另一个表的列插入到 mysql 表中

Question

我正在尝试做的看起来像下面这样

insert into user_audit 
( SELECT COLUMN_NAME
FROM information_schema.columns
WHERE TABLE_NAME='user' and TABLE_SCHEMA = database() )
VAlUES ( SELECT * FROM `user` where id = NEW.id  )

在user_audit中插入user表的列名和user表的值

感谢帮助

Answer 1

能否请您尝试以下动态 SQL:

它可能会有所帮助

declare @insertquery nvarchar(1000)
Declare @ColumnNames varchar(2000)
select @ColumnNames = COALESCE(@ColumnNames + ', ', '') + COLUMN_NAME
from 
    INFORMATION_SCHEMA.COLUMNS
where 
    TABLE_NAME='user' and TABLE_SCHEMA = database();

set @insertquery = N'insert into user_audit(' + @ColumnNames + ') SELECT * FROM `user` where id = NEW.id;

sp_executesql @insertquery