[MYSQL] Mysql 모든 컬럼 데이터 샘플 데이터

      

      

      

    
    

      <bash />
    
-- MYSQL8 ALL_DATA_TYPE DDL
-------------------------------------------------------------------------------------------
CREATE TABLE `mysql8_all_datatypes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tiny_int_col` tinyint(4) DEFAULT NULL,
  `small_int_col` smallint(6) DEFAULT NULL,
  `medium_int_col` mediumint(9) DEFAULT NULL,
  `int_col` int(11) DEFAULT NULL,
  `big_int_col` bigint(20) DEFAULT NULL,
  `decimal_col` decimal(10,2) DEFAULT NULL,
  `float_col` float(10,2) DEFAULT NULL,
  `double_col` double(10,2) DEFAULT NULL,
  `bit_col` bit(8) DEFAULT NULL,
  `char_col` char(50) DEFAULT NULL,
  `varchar_col` varchar(255) DEFAULT NULL,
  `tinytext_col` tinytext,
  `text_col` text,
  `mediumtext_col` mediumtext,
  `longtext_col` longtext,
  `enum_col` enum('Small','Medium','Large') DEFAULT NULL,
  `set_col` set('One','Two','Three') DEFAULT NULL,
  `binary_col` binary(50) DEFAULT NULL,
  `varbinary_col` varbinary(255) DEFAULT NULL,
  `tinyblob_col` tinyblob,
  `blob_col` blob,
  `mediumblob_col` mediumblob,
  `longblob_col` longblob,
  `date_col` date DEFAULT NULL,
  `time_col` time DEFAULT NULL,
  `datetime_col` datetime DEFAULT NULL,
  `timestamp_col` timestamp NULL DEFAULT NULL,
  `year_col` year(4) DEFAULT NULL,
  `json_col` json DEFAULT NULL,
  `point_col` point DEFAULT NULL,
  `linestring_col` linestring DEFAULT NULL,
  `polygon_col` polygon DEFAULT NULL,
  `multipoint_col` multipoint DEFAULT NULL,
  `multilinestring_col` multilinestring DEFAULT NULL,
  `multipolygon_col` multipolygon DEFAULT NULL,
  `geometrycollection_col` geomcollection DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `varchar_index` (`varchar_col`),
  FULLTEXT KEY `text_index` (`text_col`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8CREATE TABLE `mysql8_all_datatypes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tiny_int_col` tinyint(4) DEFAULT NULL,
  `small_int_col` smallint(6) DEFAULT NULL,
  `medium_int_col` mediumint(9) DEFAULT NULL,
  `int_col` int(11) DEFAULT NULL,
  `big_int_col` bigint(20) DEFAULT NULL,
  `decimal_col` decimal(10,2) DEFAULT NULL,
  `float_col` float(10,2) DEFAULT NULL,
  `double_col` double(10,2) DEFAULT NULL,
  `bit_col` bit(8) DEFAULT NULL,
  `char_col` char(50) DEFAULT NULL,
  `varchar_col` varchar(255) DEFAULT NULL,
  `tinytext_col` tinytext,
  `text_col` text,
  `mediumtext_col` mediumtext,
  `longtext_col` longtext,
  `enum_col` enum('Small','Medium','Large') DEFAULT NULL,
  `set_col` set('One','Two','Three') DEFAULT NULL,
  `binary_col` binary(50) DEFAULT NULL,
  `varbinary_col` varbinary(255) DEFAULT NULL,
  `tinyblob_col` tinyblob,
  `blob_col` blob,
  `mediumblob_col` mediumblob,
  `longblob_col` longblob,
  `date_col` date DEFAULT NULL,
  `time_col` time DEFAULT NULL,
  `datetime_col` datetime DEFAULT NULL,
  `timestamp_col` timestamp NULL DEFAULT NULL,
  `year_col` year(4) DEFAULT NULL,
  `json_col` json DEFAULT NULL,
  `point_col` point DEFAULT NULL,
  `linestring_col` linestring DEFAULT NULL,
  `polygon_col` polygon DEFAULT NULL,
  `multipoint_col` multipoint DEFAULT NULL,
  `multilinestring_col` multilinestring DEFAULT NULL,
  `multipolygon_col` multipolygon DEFAULT NULL,
  `geometrycollection_col` geomcollection DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `varchar_index` (`varchar_col`),
  FULLTEXT KEY `text_index` (`text_col`)
)
;

-- 인덱스 생성
CREATE INDEX varchar_index ON mysql8_all_datatypes(varchar_col);
CREATE FULLTEXT INDEX text_index ON mysql8_all_datatypes(text_col);

-------------------------------------------------------------------------------------------
PROCEDURES

-- 테스트 데이터 삽입 프로시저
CREATE PROCEDURE INSERT_TEST_DATA()
BEGIN
    INSERT INTO mysql8_all_datatypes (
        tiny_int_col, small_int_col, medium_int_col, int_col, big_int_col,
        decimal_col, float_col, double_col, bit_col,
        char_col, varchar_col, tinytext_col, text_col, mediumtext_col, longtext_col,
        enum_col, set_col,
        binary_col, varbinary_col, tinyblob_col, blob_col,
        date_col, time_col, datetime_col, timestamp_col, year_col,
        json_col
    ) VALUES (
        127, 32767, 8388607, 2147483647, 9223372036854775807,
        1234.56, 1234.56, 1234.56, b'11111111',
        'Fixed length text', 'Variable length text', 'Tiny text content', 'Regular text content',
        'Medium text content', 'Long text content',
        'Medium', 'One,Two',
        'Binary', 'Variable Binary', 'Tiny BLOB', 'Regular BLOB',
        CURRENT_DATE, CURRENT_TIME, NOW(), CURRENT_TIMESTAMP, YEAR(CURRENT_DATE),
        '{"key": "value", "number": 123}'
    );

    SET @g1 = ST_GeomFromText('POINT(1 1)');
    SET @g2 = ST_GeomFromText('LINESTRING(0 0, 1 1, 2 2)');
    SET @g3 = ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))');
    SET @g4 = ST_GeomFromText('MULTIPOINT(1 1, 2 2)');
    SET @g5 = ST_GeomFromText('MULTILINESTRING((0 0, 1 1), (2 2, 3 3))');
    SET @g6 = ST_GeomFromText('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))');
    SET @g7 = ST_GeomFromText('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(0 0, 1 1))');

    UPDATE mysql8_all_datatypes 
    SET point_col = @g1,
        linestring_col = @g2,
        polygon_col = @g3,
        multipoint_col = @g4,
        multilinestring_col = @g5,
        multipolygon_col = @g6,
        geometrycollection_col = @g7
    WHERE id = LAST_INSERT_ID();
END;

-------------------------------------------------------------------------------------------
PROCEDURES 실행
CALL INSERT_TEST_DATA();