Блог

Я вставляю запись в таблицу базы данных. Но каждый раз mysql возвращает одну и ту же ошибку.

#1136 — Количество столбцов не соответствует количеству значений в строке 1

Я использую Wamp3.1.7, MYSQL5.7.24.

Этот вопрос занял много времени. я проверил все решения, но проблему все-таки решил.

Количество столбцов (18) одинаково с обеих сторон. Запятая не пропущена. В значениях нет лишней запятой.

 INSERT INTO leads (id, 
               date_entered, 
               created_by, 
               assigned_user_id, 
               first_name, 
               last_name, 
               title, 
               phone_mobile, 
               phone_work, 
               phone_fax, 
               primary_address_street, 
               primary_address_city, 
               primary_address_state, 
               primary_address_postalcode, 
               primary_address_country,
               alt_address_street, 
               contact_id, 
               account_id) 
      VALUES ('efc776b6-78c3-f706-3abd-5ca63513288e', 
              '2019-04-04 00:00:00', 
              'd85f7ad3-5072-88f3-b5b5-5a9fee38ff1e', 
              'd85f7ad3-5072-88f3-b5b5-5a9fee38ff1e', 
              'James', 
              'Martin', 
              'ead', 
              '4453228584', 
              'asdfasdff', 
              'asdf', 
              'ABC Street', 
              'New Yark', 
              'New Yark', 
              '84000', 
              'USA', 
              'xyz Street', 
              '729ec624-83e6-ee7b-51ae-5b5212763ba5', 
              '510c016f-ec09-9b6a-abb3-5b521271149c'
       )
  

Я также попробовал формат ниже

 INSERT INTO `leads` (`id`, 
                 `date_entered`, 
                 `created_by`, 
                 `assigned_user_id`,   ......
  

Вот моя структура таблицы

 CREATE TABLE `leads` (
`id` CHAR(36) NOT NULL,
`date_entered` DATETIME NOT NULL,
`date_modified` DATETIME NULL DEFAULT NULL,
`modified_user_id` CHAR(36) NULL DEFAULT NULL,
`created_by` CHAR(36) NOT NULL,
`description` TEXT NULL,
`deleted` TINYINT(1) NOT NULL DEFAULT '0',
`assigned_user_id` CHAR(36) NULL DEFAULT NULL,
`salutation` ENUM('MISS','MISSES','MISTER') NULL DEFAULT NULL,
`first_name` VARCHAR(100) NULL DEFAULT NULL,
`last_name` VARCHAR(100) NULL DEFAULT NULL,
`title` VARCHAR(100) NULL DEFAULT NULL,
`photo` VARCHAR(255) NULL DEFAULT NULL,
`department` VARCHAR(100) NULL DEFAULT NULL,
`do_not_call` TINYINT(1) NULL DEFAULT '0',
`phone_home` VARCHAR(100) NULL DEFAULT NULL,
`phone_mobile` VARCHAR(100) NULL DEFAULT NULL,
`phone_work` VARCHAR(100) NULL DEFAULT NULL,
`phone_other` VARCHAR(100) NULL DEFAULT NULL,
`phone_fax` VARCHAR(100) NULL DEFAULT NULL,
`primary_address_street` VARCHAR(150) NULL DEFAULT NULL,
`primary_address_city` VARCHAR(100) NULL DEFAULT NULL,
`primary_address_state` VARCHAR(100) NULL DEFAULT NULL,
`primary_address_postalcode` VARCHAR(20) NULL DEFAULT NULL,
`primary_address_country` VARCHAR(255) NULL DEFAULT NULL,
`alt_address_street` VARCHAR(150) NULL DEFAULT NULL,
`alt_address_city` VARCHAR(100) NULL DEFAULT NULL,
`alt_address_state` VARCHAR(100) NULL DEFAULT NULL,
`alt_address_postalcode` VARCHAR(20) NULL DEFAULT NULL,
`alt_address_country` VARCHAR(255) NULL DEFAULT NULL,
`assistant` VARCHAR(75) NULL DEFAULT NULL,
`assistant_phone` VARCHAR(100) NULL DEFAULT NULL,
`converted` TINYINT(1) NULL DEFAULT '0',
`refered_by` VARCHAR(100) NULL DEFAULT NULL,
`lead_source` ENUM('3rd party data provider','Essent - campaign','Essent - manual entry','MOVE','Migration','Sales partner') NULL DEFAULT NULL,
`lead_source_description` TEXT NULL,
`status` ENUM('Contacted','Open','Qualified','Unqualified') NULL DEFAULT 'Open',
`status_description` TEXT NULL,
`reports_to_id` CHAR(36) NULL DEFAULT NULL,
`account_name` VARCHAR(255) NULL DEFAULT NULL,
`account_description` TEXT NULL,
`contact_id` CHAR(36) NULL DEFAULT NULL,
`account_id` CHAR(36) NULL DEFAULT NULL,
`opportunity_id` CHAR(36) NULL DEFAULT NULL,
`opportunity_name` VARCHAR(255) NULL DEFAULT NULL,
`opportunity_amount` VARCHAR(50) NULL DEFAULT NULL,
`campaign_id` CHAR(36) NULL DEFAULT NULL,
`birthdate` DATE NULL DEFAULT NULL,
`portal_name` VARCHAR(255) NULL DEFAULT NULL,
`portal_app` VARCHAR(255) NULL DEFAULT NULL,
`website` VARCHAR(255) NULL DEFAULT NULL,
`future_contact_date_c` DATE NULL DEFAULT NULL,
`gender_c` ENUM('FEMALE','MALE','UNKNOWN') NULL DEFAULT NULL,
`language_c` ENUM('UNKNOWN','fr_BE','nl_BE') NULL DEFAULT 'UNKNOWN',
`lead_channel_c` ENUM('CONFERENCE','EMAIL','INBOUND CALL','LIST','MOZA','MY ESSENT','OLR','POST','REGDOC','WEBSITE') NULL DEFAULT NULL,
`record_type` ENUM('B2B','B2C','GLN','MYS') NULL DEFAULT NULL,
`preferred_contact_hour_c` TEXT NULL,
`primary_address_addition` VARCHAR(255) NULL DEFAULT NULL,
`primary_address_bus` VARCHAR(255) NULL DEFAULT NULL,
`primary_address_number` VARCHAR(255) NULL DEFAULT NULL,
`unqualified_reason_c` ENUM('Dataquality issue','Deceased','Impossible to contact','Moved to foreign country/elderly home','Not interested','Other') NULL DEFAULT NULL,
`active_lead_c` TINYINT(1) NULL DEFAULT '1',
`nace_code_c` VARCHAR(5) NULL DEFAULT NULL,
`company_number_c` VARCHAR(15) NULL DEFAULT NULL,
`company_name_c` VARCHAR(35) NULL DEFAULT NULL,
`owner_of_company_c` TINYINT(1) NULL DEFAULT '0',
`legal_form_c` ENUM('bvba','cva','cvba','ebvba','gcv','ivzw','nv','sbvba','vof','vzw') NULL DEFAULT NULL,
`function_c` VARCHAR(50) NULL DEFAULT NULL,
`property_owner_c` TINYINT(1) NULL DEFAULT '1',
`lead_has_electricity` TINYINT(1) NULL DEFAULT '0',
`lead_has_gas` TINYINT(1) NULL DEFAULT '0',
`request_credit_check_c` TINYINT(1) NULL DEFAULT '0',
`acceptance_status_c` VARCHAR(25) NULL DEFAULT NULL,
`category` ENUM('Large','Medium','Small') NULL DEFAULT NULL,
`graydon_creditscore_c` INT(3) NULL DEFAULT NULL,
`nbr_of_employees` INT(5) NULL DEFAULT NULL,
`innogy_customer_c` TINYINT(1) NULL DEFAULT '0',
`innogy_number_c` VARCHAR(25) NULL DEFAULT NULL,
`innogy_gas_c` TINYINT(1) NOT NULL DEFAULT '0',
`innogy_elec_c` TINYINT(1) NOT NULL DEFAULT '0',
`family_key` VARCHAR(255) NULL DEFAULT NULL,
`person_key` VARCHAR(255) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_lead_acct_name_first` (`account_name`, `deleted`),
INDEX `idx_lead_last_first` (`last_name`, `first_name`, `deleted`),
INDEX `idx_lead_del_stat` (`last_name`, `status`, `deleted`, `first_name`),
INDEX `idx_lead_opp_del` (`opportunity_id`, `deleted`),
INDEX `idx_leads_acct_del` (`account_id`, `deleted`),
INDEX `idx_del_user` (`deleted`, `assigned_user_id`),
INDEX `idx_lead_phone_work` (`phone_work`),
INDEX `idx_leads_id_del` (`id`, `deleted`),
INDEX `fk_users_id_091d5d40` (`modified_user_id`),
INDEX `fk_users_id_cf53841f` (`created_by`),
INDEX `fk_users_id_83b8baf9` (`assigned_user_id`),
INDEX `fk_contacts_id_852b9161` (`contact_id`),
INDEX `fk_leads_id_531d63cd` (`reports_to_id`),
INDEX `fk_campaigns_id_ccf6fbc4` (`campaign_id`),
CONSTRAINT `fk_accounts_id_65e8b037` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
CONSTRAINT `fk_campaigns_id_ccf6fbc4` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`),
CONSTRAINT `fk_contacts_id_852b9161` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`),
CONSTRAINT `fk_leads_id_531d63cd` FOREIGN KEY (`reports_to_id`) REFERENCES `leads` (`id`),
CONSTRAINT `fk_opportunities_id_84fbff38` FOREIGN KEY (`opportunity_id`) REFERENCES `opportunities` (`id`),
CONSTRAINT `fk_users_id_091d5d40` FOREIGN KEY (`modified_user_id`) REFERENCES `users` (`id`),
CONSTRAINT `fk_users_id_83b8baf9` FOREIGN KEY (`assigned_user_id`) REFERENCES `users` (`id`),
CONSTRAINT `fk_users_id_cf53841f` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
  

В этой таблице есть 3 триггера, один из них после события insert. Вот его инструкция action.

 BEGIN
  INSERT INTO leads_aud
    SELECT now(6), 'INSERT', 0, leads.* FROM leads WHERE id = NEW.id;
END
  

Вот код таблицы lead_aud

 CREATE TABLE IF NOT EXISTS `leads_aud` (
  `id` char(36) NOT NULL,
  `parent_id` char(36) NOT NULL,
  `date_created` datetime DEFAULT NULL,
  `created_by` varchar(36) DEFAULT NULL,
  `field_name` varchar(100) DEFAULT NULL,
  `data_type` varchar(100) DEFAULT NULL,
  `before_value_string` varchar(255) DEFAULT NULL,
  `after_value_string` varchar(255) DEFAULT NULL,
  `before_value_text` text,
  `after_value_text` text,
  PRIMARY KEY (`id`),
  KEY `idx_leads_parent_id` (`parent_id`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;