php - 在 Magento 1.9 中为自定义模块创建自定义表时出错

Question

我创建了一个自定义 Magento 插件，除了数据库创建之外，一切都工作正常。这是我的 SQL 代码。

<?php
$installer = $this;
$installer->startSetup();
$installer->run("
-- DROP TABLE IF EXISTS {$this->getTable('store_city')};
CREATE TABLE '{$installer->getTable('store_city')}'(
  `city_id` int(11) unsigned NOT NULL auto_increment,
  `city_name` text NOT NULL default '',
  `is_active` smallint(6) NOT NULL default '1',
  `sort_order` int(11) unsigned NOT NULL,
  `created_time` datetime NULL,
  `update_time` datetime NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- DROP TABLE IF EXISTS {$this->getTable('store_area')};
CREATE TABLE '{$installer->getTable('store_area')}'(
  `area_id` int(11) unsigned NOT NULL auto_increment,
  `area_name` text NOT NULL default '',
  `is_active` smallint(6) NOT NULL default '1',
  `sort_order` int(11) unsigned NOT NULL,
  `area_city_id` int(11) unsigned,
  `created_time` datetime NULL,
  `update_time` datetime NULL,
  PRIMARY KEY (`area_id`),
  FOREIGN KEY (`area_city_id`) REFERENCES {$this->getTable('store_city')} (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- DROP TABLE IF EXISTS {$this->getTable('store_store')};
CREATE TABLE '{$installer->getTable('store_store')}'(
  `store_id` int(11) unsigned NOT NULL auto_increment,
  `store_name` text NOT NULL default '',
  `store_address` text NOT NULL default '',
  `long` text,
  `lat` text,
  `is_active` smallint(6) NOT NULL default '1',
  `sort_order` int(11) unsigned NOT NULL,
  `store_area_id` int(11) unsigned,
  `created_time` datetime NULL,
  `update_time` datetime NULL,
  PRIMARY KEY (`store_id`),
  FOREIGN KEY (`store_area_id`) REFERENCES {$this->getTable('store_area')} (`area_id`)
  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
");

$installer->endSetup();

但是在我复制插件并测试它后，它给了我这个错误，我该如何解决这个问题，为什么会发生这种情况？

a:5:{i:0;s:435:"Error in file: "Sites/magento/app/code/community/Test/Storelocator/sql/storelocator_setup/mysql4-install-0.1.0.php" - SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''store_city'(
  `city_id` int(11)  NOT NULL auto_increment,
  `c' at line 2";i:1;s:1124:"#0 Sites/magento/app/code/core/Mage/Core/Model/Resource/Setup.php(644): Mage::exception('Mage_Core', 'Error in file: ...')
#1 Sites/magento/app/code/core/Mage/Core/Model/Resource/Setup.php(421): Mage_Core_Model_Resource_Setup->_modifyResourceDb('install', '', '0.1.0')
#2 Sites/magento/app/code/core/Mage/Core/Model/Resource/Setup.php(327): Mage_Core_Model_Resource_Setup->_installResourceDb('0.1.0')
#3 Sites/magento/app/code/core/Mage/Core/Model/Resource/Setup.php(235): Mage_Core_Model_Resource_Setup->applyUpdates()
#4 Sites/magento/app/code/core/Mage/Core/Model/App.php(428): Mage_Core_Model_Resource_Setup::applyAllUpdates()
#5 Sites/magento/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Model_App->_initModules()
#6 Sites/magento/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#7 Sites/magento/index.php(83): Mage::run('', 'store')
#8 {main}";s:3:"url";s:24:"/magento/index.php/admin";s:11:"script_name";s:18:"/magento/index.php";s:4:"skin";s:7:"default";}

Answer 1

尝试从“text”列中删除默认值，text列不能有默认值您可以使用此代码

<?php
$installer = $this;
$installer->startSetup();
$installer->run("
-- DROP TABLE IF EXISTS {$this->getTable('store_city')};
CREATE TABLE '{$installer->getTable('store_city')}'(
  `city_id` int(11) unsigned NOT NULL auto_increment,
  `city_name` text NOT NULL,
  `is_active` smallint(6) NOT NULL default '1',
  `sort_order` int(11) unsigned NOT NULL,
  `created_time` datetime NULL,
  `update_time` datetime NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- DROP TABLE IF EXISTS {$this->getTable('store_area')};
CREATE TABLE '{$installer->getTable('store_area')}'(
  `area_id` int(11) unsigned NOT NULL auto_increment,
  `area_name` text NOT NULL ,
  `is_active` smallint(6) NOT NULL default '1',
  `sort_order` int(11) unsigned NOT NULL,
  `area_city_id` int(11) unsigned,
  `created_time` datetime NULL,
  `update_time` datetime NULL,
  PRIMARY KEY (`area_id`),
  FOREIGN KEY (`area_city_id`) REFERENCES {$this->getTable('store_city')} (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- DROP TABLE IF EXISTS {$this->getTable('store_store')};
CREATE TABLE '{$installer->getTable('store_store')}'(
  `store_id` int(11) unsigned NOT NULL auto_increment,
  `store_name` text NOT NULL ,
  `store_address` text NOT NULL ,
  `long` text,
  `lat` text,
  `is_active` smallint(6) NOT NULL default '1',
  `sort_order` int(11) unsigned NOT NULL,
  `store_area_id` int(11) unsigned,
  `created_time` datetime NULL,
  `update_time` datetime NULL,
  PRIMARY KEY (`store_id`),
  FOREIGN KEY (`store_area_id`) REFERENCES {$this->getTable('store_area')} (`area_id`)
  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
");

$installer->endSetup();