ORMのEntity自動生成時の型確認用SQL for MySQL

orm_type_checker.sql

drop table if exists full_columns;
create table full_columns(
  id bigint unsigned auto_increment comment 'id column',
  col_int int unsigned not null comment 'int column',
  col_smallint smallint unsigned not null comment 'smallint column',
  col_tinyint tinyint unsigned not null comment 'tinyint column',
  col_mediumint mediumint unsigned not null comment 'mediumint column',
  col_bigint bigint unsigned not null comment 'bigint column',
  col_decimal decimal(2) not null comment 'decimal column',
  col_float float not null comment 'float column',
  col_double double not null comment 'double column',
  col_bit bit(1) not null comment 'bit column',
  col_date date not null comment 'date column',
  col_datetime datetime not null comment 'datetime column',
  col_timestamp timestamp not null comment 'timestamp column',
  col_time time not null comment 'time column',
  col_year year not null comment 'year column',
  col_char char(10) not null comment 'char column',
  col_varchar varchar(10) not null comment 'varchar column',
  col_tinytext tinytext not null comment 'tinytext column',
  col_text text not null comment 'text column',
  col_mediumtext mediumtext not null comment 'mediumtext column',
  col_longtext longtext not null comment 'longtext column',
  col_tinyblob tinyblob not null comment 'tinyblob column',
  col_blob blob not null comment 'blob column',
  col_mediumblob mediumblob not null comment 'mediumblob column',
  col_longblob longblob not null comment 'longblob column',
  col_binary binary(10) not null comment 'binary column',
  col_varbinary varbinary(10) not null comment 'varbinary column',
  col_enum enum('red', 'blue', 'green') not null comment 'enum column',
  col_set set('cat', 'dog', 'bird') not null comment 'set column',
  col_charset char charset ascii comment 'charset column',
  col_collate char collate utf8mb4_general_ci comment 'collate column',
  col_zerofill int zerofill comment 'zerofill column',
  col_generated_virtual varchar(191) as (concat(col_char, col_varchar)) virtual comment 'generated virtual column',
  col_generated_stored varchar(191) as (concat(col_char, col_varchar)) stored comment 'generated stored column',
  primary key(id),
  key(col_char, col_varchar),
  unique key(col_tinyint, col_int)
) engine=innodb charset=utf8mb4 comment='full columns table';
show create table full_columns \G

drop table if exists child;
create table child(
  child_id bigint unsigned auto_increment comment 'child id',
  parent_id bigint unsigned comment 'parent id',
  primary key(child_id),
  foreign key fk_child_id_parent_id(parent_id) references full_columns(id)
) engine=innodb charset=utf8mb4 comment='child table';
show create table child \G

drop table if exists gis;
create table gis(
  address varchar(191) not null,
  location geometry not null,
  latlon point,
  primary key(address)
) engine=innodb charset=utf8mb4 comment='gis table';
show create table gis \G