Can this Postgresql table be converted to a MySql table?

Question

Given a Postgresql table schema:

create table thing (
    id serial primary key,
    key text,
    type int references thing,   
    latest_revision int default 1,
    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);

Answer 1

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.

Answer 2

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);
...

Answer 3

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`);