Add mysql specific ROW_FORMAT=DYNAMIC to create table statement generated by jooq

Question

I have an application which I need to backport to mysql 5.6.

This application uses rather large composite keys which works fine on mysql 5.7 because innodb-large-prefix is enabled by default.

I can configure mysql 5.6 to use innodb-large-prefix, but it also requires to create tables with ROW_FORMAT=DYNAMIC or COMPRESSED.

Here is the SQL example I would like to achieve using jooq:

CREATE TABLE `domain` (
  `path` varchar(300) NOT NULL,
  UNIQUE KEY `index1` (`path`)
) ROW_FORMAT=DYNAMIC;

These are the mysql 5.6 documentation for reference:

https://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format.html https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix

Answer 1

You can add custom storage clauses to CREATE TABLE statements by using the CreateTableStorageStep.storage() method. E.g.

ctx.createTable("domain")
   .column("path", VARCHAR(300).nullable(false))
   .constraint(constraint("index1").unique("path"))
   .storage("ROW_FORMAT=DYNAMIC")
   .execute();