Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
ORMのEntity自動生成時の型確認用SQL for MySQL
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.