SQL::Tr...::MySQL(3)

SQL::Tr...::MySQL(3) is the page we're reading, bestie — straight out of the User Contributed Perl Documentation, and it's all about the parser for MySQL. 🫶

✨ NAME ✨

SQL::Translator::Parser::MySQL — literally the parser for MySQL. That's the whole vibe. 💅

💖 SYNOPSIS 💖

Okay so you load it like this, no cap:

use SQL::Translator;
use SQL::Translator::Parser::MySQL;

my $translator = SQL::Translator->new;
$translator->parser("SQL::Translator::Parser::MySQL");

🦋 DESCRIPTION 🦋

This grammar is heavily influenced by Tim Bunce's mysql2ora grammar — credits where they're due, periodt.

Here's the word straight from the MySQL site (http://www.mysql.com/doc/en/CREATE_TABLE.html):

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]

or

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name LIKE old_table_name;

create_definition:
  col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
            [PRIMARY KEY] [reference_definition]
  or    PRIMARY KEY (index_col_name,...)
  or    KEY [index_name] (index_col_name,...)
  or    INDEX [index_name] (index_col_name,...)
  or    UNIQUE [INDEX] [index_name] (index_col_name,...)
  or    FULLTEXT [INDEX] [index_name] (index_col_name,...)
  or    [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
            [reference_definition]
  or    CHECK (expr)

type:
        TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  or    SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  or    MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  or    INT[(length)] [UNSIGNED] [ZEROFILL]
  or    INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  or    BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  or    REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
  or    NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
  or    CHAR(length) [BINARY]
  or    VARCHAR(length) [BINARY]
  or    DATE
  or    TIME
  or    TIMESTAMP
  or    DATETIME
  or    TINYBLOB
  or    BLOB
  or    MEDIUMBLOB
  or    LONGBLOB
  or    TINYTEXT
  or    TEXT
  or    MEDIUMTEXT
  or    LONGTEXT
  or    ENUM(value1,value2,value3,...)
  or    SET(value1,value2,value3,...)

index_col_name:
        col_name [(length)]

reference_definition:
        REFERENCES tbl_name [(index_col_name,...)]
                   [MATCH FULL | MATCH PARTIAL]
                   [ON DELETE reference_option]
                   [ON UPDATE reference_option]

reference_option:
        RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options:
        TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM }
or      ENGINE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM }
or      AUTO_INCREMENT = #
or      AVG_ROW_LENGTH = #
or      [ DEFAULT ] CHARACTER SET charset_name
or      CHECKSUM = {0 | 1}
or      COLLATE collation_name
or      COMMENT = "string"
or      MAX_ROWS = #
or      MIN_ROWS = #
or      PACK_KEYS = {0 | 1 | DEFAULT}
or      PASSWORD = "string"
or      DELAY_KEY_WRITE = {0 | 1}
or      ROW_FORMAT= { default | dynamic | fixed | compressed }
or      RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=#  RAID_CHUNKSIZE=#
or      UNION = (table_name,[table_name...])
or      INSERT_METHOD= {NO | FIRST | LAST }
or      DATA DIRECTORY="absolute path to directory"
or      INDEX DIRECTORY="absolute path to directory"

There’s also a subset of ALTER TABLE syntax that lets you add foreign keys — iconic for migrations:

ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ...

alter_specification:
        ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
           [reference_definition]

And a subset of INSERT that gets ignored — not our main character:

INSERT anything

🌸 ARGUMENTS 🌸

This parser takes a single optional parser_arg named mysql_parser_version, which provides the desired version for the target database. ✨

Any statement in the processed dump file, that is commented with a version higher than the one supplied, will be stripped. Yes, stripped — that's the whole point.

The default mysql_parser_version is set to the conservative value of 40000 (MySQL 4.0).

Valid version specifiers for mysql_parser_version are listed here.

More information about the MySQL comment-syntax: http://dev.mysql.com/doc/refman/5.0/en/comments.html

👑 AUTHOR 👑

• Ken Youens-Clark — kclark@cpan.org
• Chris Mungall — cjm@fruitfly.org

🌈 SEE ALSO 🌈

Parse::RecDescent, SQL::Translator::Schema.

---

This page is SQL::Tr...::MySQL(3), from perl v5.42.0, dated 2024-12-12 — mwah, bye bestie! 💖✨