开发者

Can this Postgresql table be converted to a MySql table?

Given a Postgresql table schema:

create table thing (
    id serial primary key,
    key text,
    type int references thing,   
    latest_revision int default 1,
    开发者_StackOverflow中文版created timestamp default(current_timestamp at time zone 'utc'),
    last_modified timestamp default(current_timestamp at time zone 'utc')
);
$for name in ['key', 'type', 'latest_revision', 'last_modified', 'created']:
    create index thing_${name}_idx ON thing($name);

There are two lines i do not understand and am wondering if it is even possible to convert them to a MySql table schema? Can the following line be converted to something MySql would understand, as it seems to be referencing itself:

type int references thing,

Plus, is there a MySql equivalent for the last line:

$for name in ['key', 'type', 'latest_revision', 'last_modified', 'created']:
    create index thing_${name}_idx ON thing($name);


The references line is a foreign key, you can use something like this in MySQL:

CREATE TABLE thing (
   ...
   type int,
   FOREIGN KEY (type) REFERENCES thing (id),
   ...
);

The last two lines are not in SQL, it's some scripting language. It simply creates indexes on the mentioned columns:

CREATE INDEX thing_key_idx ON thing (key);
CREATE INDEX thing_type_idx ON thing (type);
...


That last line looks like python, which would lead me to believe this came from pgloader, a commonly used python program. Or a an-adhoc python program. That is not valid syntax AFAIK in pg, or psql.

And the references foo, bit is a foreign key to the table foo's primary key. If no column is specified it defaults to the primary key.

Check the docs on create table for more info.


So, from what you all are telling me, this would be an equivalent MySql table schema of the original Postgresql table:

--
-- Table structure for table `thing`
--
CREATE TABLE IF NOT EXISTS `thing` (
  `id` int NOT NULL auto_increment,
  `key` text,
  `type` int,
  `latest_revision` tinyint NOT NULL default '1',
  `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `last_modified` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
-- 
-- Constraints for table `thing`
-- 
ALTER TABLE `thing`
  ADD CONSTRAINT `thing_ibfk_1` FOREIGN KEY (`type`) REFERENCES `thing` (`id`);
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜