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

Question

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

The `information_schema`.`columns` table won't give you everything you'll need if you want to recreate the original create table statement. You'll also need `information_schema`.`tables` which will give you the engine (probably the default innodb, but just to be sure), `statistics` will give you the keys and `key_column_usage` and `referential_constraints` any foreign key definitions. — Paul Campbell, Jul 19 '19 at 19:18

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

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.

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

1 Answers1

So;