Or at least the majority of data types. In a previous post, MySQL Sakila Structure, I went through the structure of the example sakila database and mentioned that it does not cover all supported MySQL data types. That means the example database is not sufficient on its own to test the behaviour of the MySQL Connector/J JDBC driver and how it works with different data types. In an attempt to bridge that gap I have produced a script to create a single table with all the supported data types along with modifiers that have an impact on how MySQL Connector/J represents them. See https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-type-conversions.html for more details on the nuances of the implementation’s handling of particular data types.

Create script – https://gist.github.com/pobalopalous/6d32bf24347bbd107eb8b485194b8381#file-create_mysql_all_data_types_table-sql

Data script – https://gist.github.com/pobalopalous/6d32bf24347bbd107eb8b485194b8381#file-insert_mysql_all_data_types_table-sql

MySQL has a sample database which is useful for tutorials, samples and a reference for testing integration scenarios. The scripts to create the sakila sample schema put tables, views, procedures, triggers and data in place.

The sample also includes the sakila.mwb file which is a MySQL Workbench data model that you can open within MySQL Workbench to examine the database structure. For more information, see MySQL Workbench.

Not all MySQL data types are used in the sample schema but it does cover the most common as well as a BLOB for a picture and GEOMETRY for location data. Here’s a description of each table in a convenience text structure which is searchable.

describe actor;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type              | Null | Key | Default           | Extra                                         |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| actor_id    | smallint unsigned | NO   | PRI | NULL              | auto_increment                                |
| first_name  | varchar(45)       | NO   |     | NULL              |                                               |
| last_name   | varchar(45)       | NO   | MUL | NULL              |                                               |
| last_update | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+

describe actor_info;
+------------+-------------------+------+-----+---------+-------+
| Field      | Type              | Null | Key | Default | Extra |
+------------+-------------------+------+-----+---------+-------+
| actor_id   | smallint unsigned | NO   |     | 0       |       |
| first_name | varchar(45)       | NO   |     | NULL    |       |
| last_name  | varchar(45)       | NO   |     | NULL    |       |
| film_info  | text              | YES  |     | NULL    |       |
+------------+-------------------+------+-----+---------+-------+

describe address;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type              | Null | Key | Default           | Extra                                         |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| address_id  | smallint unsigned | NO   | PRI | NULL              | auto_increment                                |
| address     | varchar(50)       | NO   |     | NULL              |                                               |
| address2    | varchar(50)       | YES  |     | NULL              |                                               |
| district    | varchar(20)       | NO   |     | NULL              |                                               |
| city_id     | smallint unsigned | NO   | MUL | NULL              |                                               |
| postal_code | varchar(10)       | YES  |     | NULL              |                                               |
| phone       | varchar(20)       | NO   |     | NULL              |                                               |
| location    | geometry          | NO   | MUL | NULL              |                                               |
| last_update | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+

describe category;
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type             | Null | Key | Default           | Extra                                         |
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
| category_id | tinyint unsigned | NO   | PRI | NULL              | auto_increment                                |
| name        | varchar(25)      | NO   |     | NULL              |                                               |
| last_update | timestamp        | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+

describe city;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type              | Null | Key | Default           | Extra                                         |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| city_id     | smallint unsigned | NO   | PRI | NULL              | auto_increment                                |
| city        | varchar(50)       | NO   |     | NULL              |                                               |
| country_id  | smallint unsigned | NO   | MUL | NULL              |                                               |
| last_update | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+

describe country;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type              | Null | Key | Default           | Extra                                         |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| country_id  | smallint unsigned | NO   | PRI | NULL              | auto_increment                                |
| country     | varchar(50)       | NO   |     | NULL              |                                               |
| last_update | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+

describe customer;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type              | Null | Key | Default           | Extra                                         |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| customer_id | smallint unsigned | NO   | PRI | NULL              | auto_increment                                |
| store_id    | tinyint unsigned  | NO   | MUL | NULL              |                                               |
| first_name  | varchar(45)       | NO   |     | NULL              |                                               |
| last_name   | varchar(45)       | NO   | MUL | NULL              |                                               |
| email       | varchar(50)       | YES  |     | NULL              |                                               |
| address_id  | smallint unsigned | NO   | MUL | NULL              |                                               |
| active      | tinyint(1)        | NO   |     | 1                 |                                               |
| create_date | datetime          | NO   |     | NULL              |                                               |
| last_update | timestamp         | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+

describe customer_list;
+----------+-------------------+------+-----+---------+-------+
| Field    | Type              | Null | Key | Default | Extra |
+----------+-------------------+------+-----+---------+-------+
| ID       | smallint unsigned | NO   |     | 0       |       |
| name     | varchar(91)       | YES  |     | NULL    |       |
| address  | varchar(50)       | NO   |     | NULL    |       |
| zip code | varchar(10)       | YES  |     | NULL    |       |
| phone    | varchar(20)       | NO   |     | NULL    |       |
| city     | varchar(50)       | NO   |     | NULL    |       |
| country  | varchar(50)       | NO   |     | NULL    |       |
| notes    | varchar(6)        | NO   |     |         |       |
| SID      | tinyint unsigned  | NO   |     | NULL    |       |
+----------+-------------------+------+-----+---------+-------+

describe film;
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field                | Type                                                                | Null | Key | Default           | Extra                                         |
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| film_id              | smallint unsigned                                                   | NO   | PRI | NULL              | auto_increment                                |
| title                | varchar(128)                                                        | NO   | MUL | NULL              |                                               |
| description          | text                                                                | YES  |     | NULL              |                                               |
| release_year         | year                                                                | YES  |     | NULL              |                                               |
| language_id          | tinyint unsigned                                                    | NO   | MUL | NULL              |                                               |
| original_language_id | tinyint unsigned                                                    | YES  | MUL | NULL              |                                               |
| rental_duration      | tinyint unsigned                                                    | NO   |     | 3                 |                                               |
| rental_rate          | decimal(4,2)                                                        | NO   |     | 4.99              |                                               |
| length               | smallint unsigned                                                   | YES  |     | NULL              |                                               |
| replacement_cost     | decimal(5,2)                                                        | NO   |     | 19.99             |                                               |
| rating               | enum('G','PG','PG-13','R','NC-17')                                  | YES  |     | G                 |                                               |
| special_features     | set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') | YES  |     | NULL              |                                               |
| last_update          | timestamp                                                           | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+

describe film_actor;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type              | Null | Key | Default           | Extra                                         |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| actor_id    | smallint unsigned | NO   | PRI | NULL              |                                               |
| film_id     | smallint unsigned | NO   | PRI | NULL              |                                               |
| last_update | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+

describe film_category;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type              | Null | Key | Default           | Extra                                         |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| film_id     | smallint unsigned | NO   | PRI | NULL              |                                               |
| category_id | tinyint unsigned  | NO   | PRI | NULL              |                                               |
| last_update | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+

describe film_list;
+-------------+------------------------------------+------+-----+---------+-------+
| Field       | Type                               | Null | Key | Default | Extra |
+-------------+------------------------------------+------+-----+---------+-------+
| FID         | smallint unsigned                  | YES  |     | 0       |       |
| title       | varchar(128)                       | YES  |     | NULL    |       |
| description | text                               | YES  |     | NULL    |       |
| category    | varchar(25)                        | NO   |     | NULL    |       |
| price       | decimal(4,2)                       | YES  |     | 4.99    |       |
| length      | smallint unsigned                  | YES  |     | NULL    |       |
| rating      | enum('G','PG','PG-13','R','NC-17') | YES  |     | G       |       |
| actors      | text                               | YES  |     | NULL    |       |
+-------------+------------------------------------+------+-----+---------+-------+

describe film_text;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| film_id     | smallint     | NO   | PRI | NULL    |       |
| title       | varchar(255) | NO   | MUL | NULL    |       |
| description | text         | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

describe inventory;
+--------------+--------------------+------+-----+-------------------+-----------------------------------------------+
| Field        | Type               | Null | Key | Default           | Extra                                         |
+--------------+--------------------+------+-----+-------------------+-----------------------------------------------+
| inventory_id | mediumint unsigned | NO   | PRI | NULL              | auto_increment                                |
| film_id      | smallint unsigned  | NO   | MUL | NULL              |                                               |
| store_id     | tinyint unsigned   | NO   | MUL | NULL              |                                               |
| last_update  | timestamp          | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+--------------+--------------------+------+-----+-------------------+-----------------------------------------------+

describe language;
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type             | Null | Key | Default           | Extra                                         |
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+
| language_id | tinyint unsigned | NO   | PRI | NULL              | auto_increment                                |
| name        | char(20)         | NO   |     | NULL              |                                               |
| last_update | timestamp        | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+------------------+------+-----+-------------------+-----------------------------------------------+

describe nicer_but_slower_film_list;
+-------------+------------------------------------+------+-----+---------+-------+
| Field       | Type                               | Null | Key | Default | Extra |
+-------------+------------------------------------+------+-----+---------+-------+
| FID         | smallint unsigned                  | YES  |     | 0       |       |
| title       | varchar(128)                       | YES  |     | NULL    |       |
| description | text                               | YES  |     | NULL    |       |
| category    | varchar(25)                        | NO   |     | NULL    |       |
| price       | decimal(4,2)                       | YES  |     | 4.99    |       |
| length      | smallint unsigned                  | YES  |     | NULL    |       |
| rating      | enum('G','PG','PG-13','R','NC-17') | YES  |     | G       |       |
| actors      | text                               | YES  |     | NULL    |       |
+-------------+------------------------------------+------+-----+---------+-------+

describe payment;
+--------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field        | Type              | Null | Key | Default           | Extra                                         |
+--------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| payment_id   | smallint unsigned | NO   | PRI | NULL              | auto_increment                                |
| customer_id  | smallint unsigned | NO   | MUL | NULL              |                                               |
| staff_id     | tinyint unsigned  | NO   | MUL | NULL              |                                               |
| rental_id    | int               | YES  | MUL | NULL              |                                               |
| amount       | decimal(5,2)      | NO   |     | NULL              |                                               |
| payment_date | datetime          | NO   |     | NULL              |                                               |
| last_update  | timestamp         | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+--------------+-------------------+------+-----+-------------------+-----------------------------------------------+

describe rental;
+--------------+--------------------+------+-----+-------------------+-----------------------------------------------+
| Field        | Type               | Null | Key | Default           | Extra                                         |
+--------------+--------------------+------+-----+-------------------+-----------------------------------------------+
| rental_id    | int                | NO   | PRI | NULL              | auto_increment                                |
| rental_date  | datetime           | NO   | MUL | NULL              |                                               |
| inventory_id | mediumint unsigned | NO   | MUL | NULL              |                                               |
| customer_id  | smallint unsigned  | NO   | MUL | NULL              |                                               |
| return_date  | datetime           | YES  |     | NULL              |                                               |
| staff_id     | tinyint unsigned   | NO   | MUL | NULL              |                                               |
| last_update  | timestamp          | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+--------------+--------------------+------+-----+-------------------+-----------------------------------------------+

describe sales_by_film_category;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| category    | varchar(25)   | NO   |     | NULL    |       |
| total_sales | decimal(27,2) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+

describe sales_by_store;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| store       | varchar(101)  | YES  |     | NULL    |       |
| manager     | varchar(91)   | YES  |     | NULL    |       |
| total_sales | decimal(27,2) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+

describe staff;
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type              | Null | Key | Default           | Extra                                         |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| staff_id    | tinyint unsigned  | NO   | PRI | NULL              | auto_increment                                |
| first_name  | varchar(45)       | NO   |     | NULL              |                                               |
| last_name   | varchar(45)       | NO   |     | NULL              |                                               |
| address_id  | smallint unsigned | NO   | MUL | NULL              |                                               |
| picture     | blob              | YES  |     | NULL              |                                               |
| email       | varchar(50)       | YES  |     | NULL              |                                               |
| store_id    | tinyint unsigned  | NO   | MUL | NULL              |                                               |
| active      | tinyint(1)        | NO   |     | 1                 |                                               |
| username    | varchar(16)       | NO   |     | NULL              |                                               |
| password    | varchar(40)       | YES  |     | NULL              |                                               |
| last_update | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-------------------+------+-----+-------------------+-----------------------------------------------+

describe staff_list;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| ID       | tinyint unsigned | NO   |     | 0       |       |
| name     | varchar(91)      | YES  |     | NULL    |       |
| address  | varchar(50)      | NO   |     | NULL    |       |
| zip code | varchar(10)      | YES  |     | NULL    |       |
| phone    | varchar(20)      | NO   |     | NULL    |       |
| city     | varchar(50)      | NO   |     | NULL    |       |
| country  | varchar(50)      | NO   |     | NULL    |       |
| SID      | tinyint unsigned | NO   |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+

describe store;
+------------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| Field            | Type              | Null | Key | Default           | Extra                                         |
+------------------+-------------------+------+-----+-------------------+-----------------------------------------------+
| store_id         | tinyint unsigned  | NO   | PRI | NULL              | auto_increment                                |
| manager_staff_id | tinyint unsigned  | NO   | UNI | NULL              |                                               |
| address_id       | smallint unsigned | NO   | MUL | NULL              |                                               |
| last_update      | timestamp         | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+------------------+-------------------+------+-----+-------------------+-----------------------------------------------+

That’s it. All the tables and views in the sakila sample database described.

Notice that sakila does not cover all MySQL data types such as TINYBLOB, VARBINARY etc. The next blog post will examine how the MySQL Connector/J JDBC driver represents that data and introduce a new table for testing all the supported data types.