Created
February 8, 2022 18:23
-
-
Save pobalopalous/6d32bf24347bbd107eb8b485194b8381 to your computer and use it in GitHub Desktop.
Create a table in MySQL with all the supported data types
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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`) | |
); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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