直接使用 CREATE TABLE … SELECT… FROM view
会提示 base table 不存在
一、创建视图表
如:v_test
CREATE OR REPLACE VIEW `v_test` AS
SELECT a.uid, a.name, b.age, b.mobile
FROM
(SELECT * FROM name WHERE pid=0) a
LEFT JOIN
(SELECT * FROM detail ) b ON (a.uid=b.uid);
二、创建有相同列的实体表
如:t_test
DROP TABLE IF EXISTS `t_test`;
CREATE TABLE `t_test` (
`uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
`name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(10) DEFAULT '0' COMMENT '年龄',
`mobile` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '业务代表',
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='信息统计表';
三、迁移数据
INSERT INTO `t_test` SELECT * FROM v_test;
MySQL视图转实体表/mysql物化试图/mysql实体化视图