Mysql:如何使物化 View 中的列成为主键

Question

我正在编写一个查询以基于物化 View 创建一个表。像

"创建 tableView AS选择 table1.column1,table2.column4,table3.column7从..."

如何更改此查询以使 table1.column1 成为此表的主键？

Answer 1

你可以这样做

CREATE TABLE tableView (PRIMARY KEY (column1)) 
SELECT table1.column1, table2.column4, table3.column7 FROM ...

参见 CREATE TABLE ... SELECT Syntax

CREATE TABLE ... SELECT does not automatically create any indexes for you. This is done intentionally to make the statement as flexible as possible. If you want to have indexes in the created table, you should specify these before the SELECT statement:

mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;

我强调的，看一个Demo原始表没有键。