MySQL 8: Create Collections via DDL

Question

I’d like to be able to create MySQL Document Store Collections via simple SQL DDL statements rather than using the X-Protocol clients.

Is there any way to do so?

Edit: I’ll try and clarify the question.

Collections are tables using JSON datatypes and functions. That much is clear. I would like know how I can create a Collection without using the X-Protocol calls and make sure that the aforementioned collection is picked up as an actual Collection.

Judging from MySQL workbench, collection tables have a _id blob PK with an expression, a doc JSON column and a few other elements I do not recall at the moment (might be indexes, etc).

I have no means to tell via the Workbench whatever additional schema/metadata information is required for a table to be considered a Document Store Collection, or if the mere presence of an _id and doc columns are enough.

I hope this clears things up.

@Akina which kind of data are you looking for, so that I may add it in the question? I’m not sure what’s missing. — raine, Jul 08 '20 at 10:23
The collection part of the document store api is a mapper for the [json datatype and json functions](https://dev.mysql.com/doc/refman/8.0/en/json.html) (or vice versa), e.g. creating a collection is basically the same as creating a table with a json column using sql syntax. You'll need to add more details if you have a specific problem or question. — Solarflare, Jul 08 '20 at 10:23

Solarflare · Accepted Answer · 2020-07-08T14:04:12.687

All "x-api" instructions are directly mapped to sql syntax. When you e.g. run db.createCollection('my_collection'), MySQL will literally just execute

CREATE TABLE `my_collection` (
  `doc` json DEFAULT NULL,
  `_id` varbinary(32) GENERATED ALWAYS AS
     (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
  `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
  PRIMARY KEY (`_id`),
  CONSTRAINT `$val_strict` CHECK (json_schema_valid(`_json_schema`,`doc`))
      NOT ENFORCED
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

You can run the corresponding sql statements yourself if you follow that format. The doc and _id (with their type and the given expression) are required, the _json_schema is optional, the check too (and only added since MySQL 8.0.17). Since MySQL 8, no additional columns are allowed, except generated columns that use JSON_EXTRACT on doc and which are supposed to be used in an index, see below (although they don't actually have to be used in an index).

Any table that looks like that - doc and _id with their correct type/expression and no other columns except an optional _json_schema and generated JSON_EXTRACT(doc,-columns - will be found with getCollections().

To add an index, the corresponding syntax for

my_collection.createIndex("age", {fields: [{field: "$.age", type: "int"}]})

would be

ALTER TABLE `test`.`my_collection` ADD COLUMN `$ix_i_somename` int
      GENERATED ALWAYS AS (JSON_EXTRACT(doc, '$.age')) VIRTUAL, 
   ADD INDEX `age` (`$ix_i_somename`)

Obviously,

db.dropCollection('my_collection')

simply translates to

DROP TABLE `my_collection`

Similarly, all CRUD operations on documents have a corresponding sql DML syntax (that will actually be executed when you use them via x-api).

Thank you, sorry for not accepting this as an answer sooner, did not receive any notification. — raine, Jul 09 '20 at 02:21

MySQL 8: Create Collections via DDL

1 Answers1