PrestaShop模块安装时SQL语法错误排查求助

核心问题分析 #

报错提示在执行CREATE TABLE ps_wallet_history时出现语法错误，主要有两个关键原因：

• PrestaShop的数据库操作类默认不允许一次性执行多条SQL语句，你把两个CREATE TABLE写在同一段SQL里，会导致解析失败。
• SQL本身存在无效语法：TEXT(255)是错误写法，TEXT类型不需要指定长度；另外id字段未设置自增，后续插入数据会因主键重复报错。

修正后的SQL代码 #

拆分两条SQL语句，同时修正字段问题：

钱包表创建语句 #

CREATE TABLE `PREFIX_wallet` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `id_customer` INT NOT NULL,
    `cash` INT DEFAULT 0,
    `data_add` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `data_upd` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`id_customer`) REFERENCES `PREFIX_customer`(`id_customer`)
);

钱包历史表创建语句 #

CREATE TABLE `PREFIX_wallet_history` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `id_customer` INT NOT NULL,
    `id_wallet` INT NOT NULL,
    `last_amount` INT NOT NULL,
    `summary` VARCHAR(255) NOT NULL,
    `data_add` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `data_upd` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`id_customer`) REFERENCES `PREFIX_customer`(`id_customer`),
    FOREIGN KEY (`id_wallet`) REFERENCES `PREFIX_wallet`(`id`)
);

模块中正确执行方式 #

在模块的install()函数中，需要分别执行两条SQL，示例代码如下：

public function install()
{
    if (!parent::install()) {
        return false;
    }

    // 替换表前缀
    $sqlWallet = str_replace('PREFIX_', _DB_PREFIX_, 'CREATE TABLE `PREFIX_wallet` (
        `id` INT NOT NULL AUTO_INCREMENT,
        `id_customer` INT NOT NULL,
        `cash` INT DEFAULT 0,
        `data_add` DATETIME DEFAULT CURRENT_TIMESTAMP,
        `data_upd` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        FOREIGN KEY (`id_customer`) REFERENCES `PREFIX_customer`(`id_customer`)
    );');
    
    $sqlHistory = str_replace('PREFIX_', _DB_PREFIX_, 'CREATE TABLE `PREFIX_wallet_history` (
        `id` INT NOT NULL AUTO_INCREMENT,
        `id_customer` INT NOT NULL,
        `id_wallet` INT NOT NULL,
        `last_amount` INT NOT NULL,
        `summary` VARCHAR(255) NOT NULL,
        `data_add` DATETIME DEFAULT CURRENT_TIMESTAMP,
        `data_upd` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        FOREIGN KEY (`id_customer`) REFERENCES `PREFIX_customer`(`id_customer`),
        FOREIGN KEY (`id_wallet`) REFERENCES `PREFIX_wallet`(`id`)
    );');

    // 分别执行两条SQL
    if (!Db::getInstance()->execute($sqlWallet) || !Db::getInstance()->execute($sqlHistory)) {
        return false;
    }

    return true;
}

额外优化说明 #

• 给data_upd添加ON UPDATE CURRENT_TIMESTAMP，可让字段在数据更新时自动刷新时间，符合PrestaShop常规设计。
• 将Summary改为小写summary，和PrestaShop默认小写字段名风格保持一致。
• 保留反引号，避免字段名与MySQL关键字冲突，属于安全写法。

内容的提问来源于stack exchange，提问作者Erfan hossinezadeh