在CREATE TABLE...SELECT建表操作中如何设置目标表主键
将
e.id设为新表主键的正确实现方式 之前尝试在SELECT子句里写PRIMARY KEY(e.id)执行失败,本质是违反了MySQL建表语法规则:SELECT子句只负责返回查询的列和对应数据,不能在这个位置定义表的约束属性,主键这类结构定义必须放在语法允许的位置。结合你提到的「来源表employees的id本身就是主键,查询结果里的id天然满足唯一、非空的要求」,有两种可以直接落地的实现方法:
方法1:在CREATE TABLE的结构定义块里声明主键(一步完成,推荐)
按照MySQL的CREATE TABLE ... SELECT语法规则,在表名后面、SELECT查询之前,用括号包裹表结构定义,把id的字段属性、主键约束写在这个块里,后续SELECT查询出的同名列会自动匹配前面定义的列填充数据:
CREATE TABLE survey_results_activities_temp ( -- 这里的字段类型要和源表employees的id类型完全一致,比如源表id是INT UNSIGNED就写INT UNSIGNED id <和源表id一致的字段类型> NOT NULL PRIMARY KEY ) SELECT e.id, taxyear, employee_email, w2_wages, employee_title, department, job_code, cc.cost_center_name, e.consultant, pr.cost_center as cost_center_pharma, pr.risk as risk_pharma FROM employees e, cost_center cc, pharma_risk pr WHERE e.email='$_SESSION[userid]' AND e.campaign= '$_SESSION[campaign]' AND e.qualified=1 AND cc.id=e.department AND cc.pharma_like=pr.id
这个方法不需要拆成两步执行,建表过程中MySQL会自动把SELECT查出来的e.id值填充到前面定义的id列,不会生成重复列。如果需要给其他字段指定长度、非空属性,也可以在这个结构块里补充,避免数据类型和预期不符。
方法2:先建表导数据,再追加主键(适合查询列很多的场景)
如果查询涉及的字段太多,不想逐一定义每个字段的类型,可以先执行你原本的不带主键定义的建表语句,把临时表和数据创建完成后,再单独执行ALTER语句加主键:
- 运行原SQL生成
survey_results_activities_temp表 - 执行追加主键的语句:
ALTER TABLE survey_results_activities_temp ADD PRIMARY KEY (id);
因为你查询出来的id来自employees表的主键,值没有重复、没有空值,这个ALTER操作不会报执行错误。
小提示:建完表后可以检查下id字段的类型,如果和源表id类型不一致,后续做关联查询时可能因为隐式类型转换拖慢查询速度。
内容的提问来源于stack exchange,提问作者user14515052
相关产品推荐
相关产品推荐

