Search code examples
mysqlcreate-tableinformation-schema

Which column attributes in a MySQL statement can specified in a CREATE TABLE / ALTER TABLE statement?

Task at Hand : Need to create tables in MySQL basis a dump from the columns table from Information.Schema of another database. I do not have access to the original DB or the DB architect. I would like to know which of the following attributes found in extracts from the Information_Schema of the original database CAN be specified in CREATE TABLE statements. The objective is to create a Tables which are ditto as per the original.

Problem at Hand: I understand that some of these attributes are specified by the user while creating tables while some may be calculated by MySQL from the data in tables. While I am reading and understanding on each of these attributes, I am unable to quickly ascertain which of the attributes listed below are calculated by MySQL and not user specified and hence can be ignored while writing CREATE TABLE statements.

CHARACTER_MAXIMUM_LENGTH
CHARACTER_OCTET_LENGTH
NUMERIC_PRECISION
NUMERIC_PRECISION_RADIX
NUMERIC_SCALE
DATETIME_PRECISION
CHARACTER_SET_CATALOG
CHARACTER_SET_SCHEMA
CHARACTER_SET_NAME
COLLATION_CATALOG
COLLATION_SCHEMA
COLLATION_NAME
DOMAIN_CATALOG
DOMAIN_SCHEMA
DOMAIN_NAME

Solution

  • Please read the MySQL Manual Page

    For CHAR and VARCHAR and similar character (non-TEXT) columns; the CHARACTER_MAXIMUM_LENGTH value is just that;

    CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
   species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
                  ^^^^          ^^^ 
                   This is the CHARACTER_MAXIMUM_LENGTH value of this column.

    CHARACTER_OCTET_LENGTH should be the same as CHARACTER_MAXIMUM_LENGTH, except for multi-byte character sets.

    For multi-byte characters sets you should probably be using utf8mb4_ sets. So the CHARACTER_OCTET_LENGTH may be CHARACTER_MAXIMUM_LENGTH x 4 (for 4-byte full UTF-8; note that utf8_ MySQL character sets/collations are 3-byte only). While this value is auto-generated upon table creation, if you're building these data sets manually you might need to calculate this one yourself.

    Which character set you use will also relate to which CHARACTER_SET_SCHEMA and CHARACTER_SET_NAME is set. For example utf8mb4_general_ci would

    For NUMERIC_* values; please read here

    • COLLATION_* (ex Schema) stuff is user set but if not user set then database and/or table defaults are used.

    • CHARACTER_SET_* (ex Schema) stuff is user set but if not user set then database and/or table defaults are used.

    • DATETIME_PRECISION is database/OS set and is not set on TABLE CREATE

    • DOMAIN_* values are not found on the MySQL Manual and seem to be invalid

    So;

    CHARACTER_MAXIMUM_LENGTH - User set *
    CHARACTER_OCTET_LENGTH - Auto generated, from User set/default details (*)
    NUMERIC_PRECISION - User set
    NUMERIC_PRECISION_RADIX - User set for specific spacial-type columns
    NUMERIC_SCALE - User set
    DATETIME_PRECISION - System set
    CHARACTER_SET_CATALOG - System set catalogue of possible values.
    CHARACTER_SET_SCHEMA - The generated Schema of possible values from the Catalogue
    CHARACTER_SET_NAME - User set but defaults to MySQL / Db default values *
    COLLATION_CATALOG - System set catalogue of possible values.
    COLLATION_SCHEMA - The generated Schema of possible values from the Catalogue
    COLLATION_NAME - User set but defaults to MySQL / Db default values *
    DOMAIN_CATALOG - Unknown. no records of this type.
    DOMAIN_SCHEMA - Unknown. no records of this type.
    DOMAIN_NAME - Unknown. no records of this type.