Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Create a table in MySQL with all the supported data types
CREATE TABLE `all_data_types` (
`dt_int` int NOT NULL,
`dt_varchar` varchar(45) DEFAULT NULL,
`dt_decimal` decimal(4,3) DEFAULT NULL,
`dt_datetime` datetime DEFAULT NULL,
`dt_blob` blob,
`dt_binary` binary(255) DEFAULT NULL,
`dt_blob_255` tinyblob,
`dt_longblob` longblob,
`dt_mediumblob` mediumblob,
`dt_tinyblob` tinyblob,
`dt_varbinary` varbinary(255) DEFAULT NULL,
`dt_date` date DEFAULT NULL,
`dt_datetime_6fsp` datetime(6) DEFAULT NULL,
`dt_time` time DEFAULT NULL,
`dt_timestamp_6fsp` timestamp(6) NULL DEFAULT NULL,
`dt_year_4` year DEFAULT NULL,
`dt_geometry` geometry DEFAULT NULL,
`dt_geomery_coll` GEOMETRYCOLLECTION DEFAULT NULL,
`dt_linestring` linestring DEFAULT NULL,
`dt_multilinestring` multilinestring DEFAULT NULL,
`dt_multipoint` multipoint DEFAULT NULL,
`dt_multipolygon` multipolygon DEFAULT NULL,
`dt_point` point DEFAULT NULL,
`dt_bigint` bigint DEFAULT NULL,
`dt_double` double DEFAULT NULL,
`dt_float` float DEFAULT NULL,
`dt_mediumint` mediumint DEFAULT NULL,
`dt_real` double DEFAULT NULL,
`dt_smallint` smallint DEFAULT NULL,
`dt_tinyint` tinyint DEFAULT NULL,
`dt_char` char(1) DEFAULT NULL,
`dt_json` json DEFAULT NULL,
`dt_nchar` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`dt_nvarchar` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`dt_longtext` longtext,
`dt_mediumtext` mediumtext,
`dt_text` text,
`dt_tinytext` tinytext,
`dt_bit_10` bit(10) DEFAULT NULL,
`dt_boolean` tinyint(1) DEFAULT NULL,
`dt_enum` enum('LOW','MEDIUM','HIGH') DEFAULT NULL,
`dt_set` set('Long','Short','Wide','Narrow','Light','Heavy') DEFAULT NULL,
PRIMARY KEY (`dt_int`)
);
INSERT INTO `ords_test`.`all_data_types`
(`dt_int`, `dt_varchar`, `dt_decimal`, `dt_datetime`, `dt_blob`, `dt_binary`, `dt_blob_255`,
`dt_longblob`,
`dt_mediumblob`,
`dt_tinyblob`,
`dt_varbinary`,
`dt_date`, `dt_datetime_6fsp`, `dt_time`, `dt_timestamp_6fsp`, `dt_year_4`,
`dt_geometry`, `dt_geomery_coll`, `dt_linestring`,
`dt_multilinestring`, `dt_multipoint`, `dt_multipolygon`, `dt_point`,
`dt_bigint`, `dt_double`, `dt_float`, `dt_mediumint`, `dt_real`, `dt_smallint`, `dt_tinyint`,
`dt_char`, `dt_json`, `dt_nchar`, `dt_nvarchar`, `dt_longtext`, `dt_mediumtext`, `dt_text`, `dt_tinytext`,
`dt_bit_10`, `dt_boolean`, `dt_enum`, `dt_set`)
VALUES ('12', 'Testing 123', '9.999', NOW(), 0x6210341fab6210341, b'1010', x'C9CBBBCCCEB9C8CABCCCCEB9C9CBBB',
0x697275726520636f6d6d6f646f2076656c6974206578204c6f72656d20656120656975736d6f64206c61626f726973206e69736920646f2076656c6974206e69736920757420696e6369646964756e742065782073696e74206e756c6c612065782073697420756c6c616d636f206d696e696d20636f6e73656374657475722065786365707465757220616c6971756970206465736572756e7420656e696d2063696c6c756d20636f6e73656374657475722076656e69616d206e69736920696e20696e20657865726369746174696f6e2073756e7420726570726568656e646572697420616e696d206e6f737472756420636f6d6d6f646f20636f6e7365637465747572206465736572756e742063757069646174617420616c697175612074656d706f72206c61626f7269732070617269617475722063756c7061206e6f6e2063757069646174617420616420657373652065737365206c61626f726520656c6974206e6f73747275642073756e74206e756c6c61206d696e696d207175692071756973206c61626f726973206475697320656c697420667567696174206e6f6e20766f6c75707461746520637570696461746174206e6f737472756420656c69742074656d706f72206578206f66666963696120697073756d2063696c6c756d206f66666963696120616e696d2063757069646174617420766f6c757074617465206c61626f72756d206d61676e61204c6f72656d206d6f6c6c6974204c6f72656d20726570726568656e646572697420616c69717561206c61626f7265206e6f6e20616c69717561207175697320696e20616c6971756120657865726369746174696f6e2070726f6964656e74206575206e756c6c612070726f6964656e74206561206f6363616563617420696420636f6e7365637465747572206574,
0x5155635a4344446a6b545a654e4273465271416d6a626e5743436b69466f586954484b544b785646514a5a57754f6a7162587642714d4248686575674f4a6e745a6f46746f4670686569764653504a6979686a6769794c4f4a7a544d4d6f50675a786264,
x'C9CBBBCCCEB9C8CABCCCCEB9C9CBBB',
x'C9CBBBCCCEB9C8CABCCCCEB9C9CBBB',
CURDATE(), NOW(), NOW(), NOW(), 2022,
ST_GeomFromText('POLYGON((0 5, 2 5, 2 7, 0 7, 0 5))'), GeometryCollection(ST_GeomFromText('POINT(1 1)'), ST_GeomFromText('LINESTRING(2 1, 6 6)')), LineString(Point(1,2), Point(6,6)),
MultiLineString(ST_GeomFromText('LINESTRING(2 1, 6 6)'), ST_GeomFromText('LINESTRING(4 5, 6 6)')), MultiPoint(Point(1,2), Point(6,6)), MultiPolygon(ST_PolygonFromText('POLYGON((-74.13591384887695 40.93750722242824,-74.13522720336914 40.929726129575016,-74.15102005004883 40.9329683629703,-74.14329528808594 40.94256444133327, -74.13591384887695 40.93750722242824))')), Point(6,6),
198705122006041001, 32.5, 1196.104, 999999, 0.00001, 9999, 99,
'Y', '{"key1": "value1", "key2": "value2"}', 'Scott', 'MySQL', 'Longtext goes here', 'Medium text', 'Text Data Type', 'Tiny',
b'1010111101', false, 'MEDIUM', 'Long,Wide,Heavy');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment