MySQLDatatypes.sql

create table jemp (a json, b int);
drop table jemp;
select * from jemp;
-- Define a virtual index https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html
create table jemp (a json, b int generated always as (a->'$.a'), index i (b));
insert into jemp (a) values ('{"a":123}');

-- https://dev.mysql.com/doc/refman/5.7/en/json.html MySQL 5.7.8
select JSON_TYPE("1");
select JSON_ARRAY(1, "", NOW());
select JSON_OBJECT('a', 1, 'b', 2);
select JSON_MERGE('1','2','3');
select JSON_EXTRACT('{"a":1}', '$.a');

set @j = JSON_OBJECT('a', 1, 'b', 2);
select @j;
SELECT CHARSET(@j), COLLATION(@j);
-- select JSON_EXTRACT(@j, '$.a') Why doesn't this work?

-- https://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html
create table num_types(
  a bit, 
  a1 bit(8), 
  b tinyint, -- 8-bits unsigned by default
  b1 tinyint signed, -- -128-127
  b2 tinyint unsigned, -- 0-255
  b3 tinyint zerofill, -- Q?
  b4 tinyint(1), 
  c boolean, -- alias for tinyint(1); true/false alias for 0 and 1
  d smallint,
  d1 smallint unsigned, -- 0-64k
  e mediumint, -- 16bits
  f int, -- 32 bits unsigned max 	4294967295
  g bigint, -- 64 bits unsigned max 18446744073709551615; can be stored as a string to avoid double conversion!
  h serial, -- SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
  i decimal,
  i1 decimal (10, 5), -- precision (total digits) 10, scale 5 (num digits to after decimal). 65 and 30 are max, alias fixed, dec
  j float, -- IEEE single precision float; all calculations use double in mysql!
  j1 float (10,5),
  j2 float(32), -- specify bits, which means mysql determines whether to use float or double.
  k double,
  k1 double(10,5)
);

insert into num_types(b3) values (256);
select * from num_types;

  -- https://dev.mysql.com/doc/refman/5.7/en/date-and-time-type-overview.html
  -- Gonna ignore fractional seconds
create table date_types(
  a date, -- The supported range is '1000-01-01' to '9999-12-31'; YYYY-MM-DD supports both strings and numbers
  b datetime, -- '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'
  c timestamp, -- '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' seconds since epoch
  d time, -- The range is '-838:59:59.000000' to '838:59:59.000000'. MySQL displays TIME values in 'HH:MM:SS[.fraction]' format, 
  e year
);

insert into date_types(a) values ('2017-06-20');
insert into date_types(a) values ('2017/06/20');
insert into date_types(a) values ('2017.06.20');
insert into date_types(a) values ('2017 06 20'); -- nope!
insert into date_types(a) values ('2017#06#20');
insert into date_types(a) values ('JUN 20 2017');

-- https://dev.mysql.com/doc/refman/5.7/en/string-type-overview.html
-- default encoding is utf8, can be set per field.
-- skipped blobs
-- https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html#data-types-storage-reqs-innodb
create table string_types(
  a varchar(2), -- stored inline, with the table's 64k row limit
  b tinytext, -- up to 255 chars
  c text, -- up to 64k chars
  c1 text(10), -- picks a datatype that can fit that many characters
  d mediumtext, -- up to 16M characters
  e longtext -- up to 4GB characters; used for json data; does not count against 64k row limit
);
drop table string_types;

insert into string_types(b) values ('asdfesdfsdfssdfasdfasdfsafasdfasfdfsdfsdfsdfsafdasdfsadfsadfsadfasdfsadfsadffasdfasdfsadfasdfsadfasdfasdfasdfasdfasdfasdfasdfsadfasdfadsfasdfasdfasdfasdfsadfasdfasdfsadfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfasdfadfasdfasdfasdfasdfasdfasdfasdfasddf');
select * from string_types;