创建表“别名”,并更改表类型
我有一个名为 tb 的表,它是 innodb - 拥有超过 100 万行。
我想对表进行全文搜索,但我不能这样做,因为它是 innodb。
那么,如何创建一个名为 tb2 的 tb 的“别名”,意思是(表格的副本,当 的数据更新时自动更新) tb 更新,但将 tb2 的表类型更改为 myisam )?
这样,我就可以对 tb2 进行全文搜索,找到 id 并从 tb1 中选择 id
I have a table called tb which is innodb - has over 1m rows.
I want to do a fulltext search on the table, but I can't do that because its innodb.
So, How do I create an "alias" of tb called tb2, meaning (a duplicate of the table, that updates automatically when the data of tb updates, but change the table type of tb2 to myisam )?
So, that way I can do a fulltext search on tb2, find the ids and SELECT the ids from tb1
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
我个人认为这并不是一个好主意,因为它会减慢写入速度,并且随着您拥有的数据量的增加,您的全文搜索也会很慢。我真诚地建议您研究一个专用的全文搜索引擎,例如 Sphinx
http://sphinxsearch.com/
I personally think that this is not really a good idea because it will slow down writes and with the amount of data you have, your fulltext searches will be slow. I would sincerely recommend that you look into a dedicated full text search engine like Sphinx
http://sphinxsearch.com/
但要小心,因为这会让写操作变慢。
But be careful, because it will make the write operations slower.
首先,您将使用以下语法创建一个表:
这将创建一个与 tb 具有相同索引结构的表。然后,更改表以使其成为 myisam:
然后,复制现有信息:
为了维护信息,您需要在 tb 上创建三个触发器...
after insert
,更新后
和删除后
。每个事件都会插入/更新/删除 tb2 中的相应行。First, you would create a table using the following syntax:
This creates a table with the same index structure as tb. Then, alter the table to make it myisam:
Then, copy over the existing information:
In order to maintain the info, you would then need to create three triggers on tb...
after insert
,after update
, andafter delete
. Each event would insert / update / delete the corresponding row in tb2.