php - 导致列计数与第 1 行的值计数不匹配的 SQL 语句有什么问题？

Question

Mysql表(migration_terms)字段如下

oldterm   count    newterm  seed

我使用了以下创建表语句。

CREATE TABLE `migration_terms` 
    ( 
      `oldterm` varchar(255) DEFAULT NULL, 
      `count` smallint(6) DEFAULT '0', 
      `newterm` varchar(255) DEFAULT NULL, 
      `seed` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`seed`) 
    )   ENGINE=InnoDB DEFAULT CHARSET=utf8

它工作正常，没有问题。

但是当我使用下面的 insert into 语句来填充它时；

"INSERT INTO migration_terms 
     SELECT looseterm as oldterm, 
            COUNT(seed) AS count 
            FROM looseterms 
            GROUP BY looseterm 
            ORDER BY count DESC "

我收到这个错误；

 Column count doesn't match value count at row 1

我想不通为什么？

如果需要looseterms表的表结构，可以通过下面的create table语句创建。

CREATE TABLE looseterms
(
     `seed` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     `looseterm` varchar(255)
)

Answer 1

如果您的 select 语句的列数少于表格，则需要指定列数

"INSERT INTO migration_terms 
        (oldterm, 
         count) 
SELECT looseterm   AS oldterm, 
       Count(seed) AS count 
FROM   looseterms 
GROUP  BY looseterm 
ORDER  BY count DESC  "

来自 MySql docs on Insert Syntax

If you do not specify a list of column names for INSERT ... VALUES or INSERT ... SELECT, values for every column in the table must be provided by the VALUES list or the SELECT statement. If you do not know the order of the columns in the table, use DESCRIBE tbl_name to find out.