Mysql错误输出:列计数与第1行的值计数不匹配

Question

SQL查询：

INSERT INTO probid_users 
(username, password, email, reg_date, payment_mode, balance, max_credit, salt, tax_account_type, tax_company_name, tax_reg_number, tax_apply_exempt, name, address, city, country, state, zip_code, phone, birthdate, birthdate_year, newsletter, pg_paypal_email, pg_worldpay_id, pg_checkout_id, pg_nochex_email, pg_ikobo_username, pg_ikobo_password, pg_protx_username, pg_protx_password, pg_authnet_username, pg_authnet_password, pg_mb_email, pg_paymate_merchant_id, pg_gc_merchant_id, pg_gc_merchant_key, pg_amazon_access_key, pg_amazon_secret_key, pg_alertpay_id, pg_alertpay_securitycode, pg_gunpal_id, first_name, last_name, pg_account_number, pg_account_holder_name, pg_account_holders_number, pg_bank_name, pg_bank_code, pg_paypal_percent_fee, pg_paypal_flat_fee ) 
VALUES 
('42685', '**181e6bd87b116b270bef7c308fd2afdb**', 'jose_jamo@hotmail.com', **1368979346**, **2**, '-5', '5.00', 'dc1', '0', '', '', '0', 'vv fvf', 'fvfvf', 'fvfv', '2083', '2173', '4262', '28823625', '1986-01-01', '1986', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'vv', 'fvf', '', '','', '', '')

错误在哪里？在？*之间

Answer 1

查询的“插入”部分指定50列，但“值”仅指定48列。您可以再添加两个空字符串值来平衡它。

INSERT INTO probid_users (username, password, email, reg_date, payment_mode, balance, max_credit, salt, tax_account_type, tax_company_name, tax_reg_number, tax_apply_exempt, name, address, city, country, state, zip_code, phone, birthdate, birthdate_year, newsletter, pg_paypal_email, pg_worldpay_id, pg_checkout_id, pg_nochex_email, pg_ikobo_username, pg_ikobo_password, pg_protx_username, pg_protx_password, pg_authnet_username, pg_authnet_password, pg_mb_email, pg_paymate_merchant_id, pg_gc_merchant_id, pg_gc_merchant_key, pg_amazon_access_key, pg_amazon_secret_key, pg_alertpay_id, pg_alertpay_securitycode, pg_gunpal_id, first_name, last_name, pg_account_number, pg_account_holder_name, pg_account_holders_number, pg_bank_name, pg_bank_code, pg_paypal_percent_fee, pg_paypal_flat_fee ) 
VALUES ('42685', '181e6bd87b116b270bef7c308fd2afdb', 'jose_jamo@hotmail.com', 1368979346, 2, '-5', '5.00', 'dc1', '0', '', '', '0', 'vv fvf', 'fvfvf', 'fvfv', '2083', '2173', '4262', '28823625', '1986-01-01', '1986', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'vv', 'fvf', '', '','', '', '', '', '')

Answer 2

我不打算计算所有这些字段，但问题可能是“在SQL中”是一种逃避的方法。与其对空字符串使用“”，不如尝试\'\‘或“'’”。很困惑，我知道。

Answer 3

列计数与值计数不匹配。

为防止今后发生这种情况：

如果有可能没有值(可能查询中所有字段都带有空字符串'‘)，则应该添加一个选项，即某个列可以是NULL。

CREATE TABLE tbl(
    field1 int NULL /* INSTEAD OF NOT NULL */
)

Null表示您还不知道值，或者没有值。然后您就可以执行这样的语句：

INSERT INTO probid_users 
(username, password, email, reg_date, payment_mode, balance, max_credit, salt, tax_account_type, tax_apply_exempt, name, address, city, country, state, zip_code, phone, birthdate, birthdate_year, newsletter, pg_paypal_email, pg_account_number, pg_account_holder_name) 
VALUES 
('42685', '**181e6bd87b116b270bef7c308fd2afdb**', 'jose_jamo@hotmail.com', **1368979346**, **2**, '-5', '5.00', 'dc1', '0', '', '', '0', 'vv fvf', 'fvfvf', 'fvfv', '2083', '2173', '4262', '28823625', '1986-01-01', '1986', 'vv', 'fvf')

如果您只插入可用的值，而不是所有的值，您就可以防止将来出现这样的错误。

顺便说一句:您还应该看看数据库规范化(normalization)。