将字段复制到另一个表。

I have a table posts that could look like this:

我有这样的桌子:

  id  |  title  |  body  |  created  | ..
-------------------------------------------

I would like to use the boolean search feature that is offered by a MyISAM Table, but the posts table is InnoDB. So I created another table 'post_contents' that looks like this:

我想使用MyISAM表提供的布尔搜索特性，但是posts表是InnoDB。所以我创建了另一个表“post_contents”，它看起来是这样的:

  post_id  |  body
--------------------

That table is already filled with some contents and I can use the boolean search. However, I need to move the title field in the post_contents table as well and then copy the existing title-data to the new field.

该表已经填充了一些内容，我可以使用布尔搜索。但是，我还需要将post_contents表中的title字段移到新字段中，然后将现有的标题数据复制到新字段中。

I know about the INSERT .. SELECT syntax, but I don't seem to be able to create the correct query.

我知道插入的事。选择语法，但是我似乎不能创建正确的查询。

2 个解决方案

#1

Did you try

你试过

insert into post_contents (post_id, body) select id, body from posts;

Or is the post_id column in the post_contents table generated differently?

还是post_contents表中的post_id列有不同的生成?

#2

I found a way:

我找到了一个方法:

I copied the the post_contents table to pc and truncated the existing data in post_contents. Then I used that query

我将post_contents表复制到pc并截断post_contents中的现有数据。然后我使用了那个查询。

INSERT INTO post_contents (post_id, title, body, created, modified) 
SELECT post.id, post.title, pc.body, pc.draft, pc.created, pc.modified 
FROM posts 
INNER JOIN pc ON post.id = pc.post_id

Maybe that is helpful for other people :)

也许这对其他人有帮助:)

#1