将查询结果插入数据库行

Question

我有一个表，用于生成第二个表，结果如下：

INSERT INTO payment_transactions_daily_facts (count, volume, date, year, month, week, day, transaction_type, contract_id, merchant_id, terminal_id, status, card_brand, currency)
SELECT
        COUNT(*) count,
        SUM(amount) volume,
        DATE(created_at) date,
        YEAR(created_at) year,
        MONTH(created_at) month,
        WEEK(created_at) week,
        DAY(created_at) day,
        type transaction_type,
        contract_id, merchant_id, terminal_id,
        status, card_brand, currency
        FROM payment_transactions
        WHERE created_at BETWEEN '2018-11-11' AND '2018-11-14'
        GROUP by date, contract_id, merchant_id, terminal_id, transaction_type, status, card_brand, currency

当我只执行select语句时，我得到：

count   volume  date    year    month   week    day transaction_type    contract_id merchant_id terminal_id status  card_brand  currency
1   3000    2018-11-11  2018    11  45  11  Sale    1   2   1   approved    NULL    USD

但我不能执行插入。我得到了错误：

Field 'id' doesn't have a default value

我使用I序列：

CREATE TABLE `payment_transactions_daily_facts` (
  `id` int(11) NOT NULL,
  `card_brand` varchar(255) DEFAULT NULL,
  `contract_id` int(11) DEFAULT NULL,
  `count` int(11) DEFAULT NULL,
  `currency` varchar(255) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `day` int(11) DEFAULT NULL,
  `merchant_id` int(11) DEFAULT NULL,
  `month` int(11) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `terminal_id` int(11) DEFAULT NULL,
  `transaction_type` varchar(255) DEFAULT NULL,
  `volume` int(11) DEFAULT NULL,
  `week` int(11) DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

我怎么能用序列作为id？

Answer 1

您需要id字段的AUTO_INCRMENT默认值，因此：

ALTER TABLE `payment_transactions_daily_facts`
CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT FIRST;

然后，下次运行INSERT时，系统将自动为您创建一个自动增量id。