MySql Export Syntax question

Ask the Community
1

Hi,

I am new to dbdiagram.io

I have a table definition as

Table entity
{
  id bigint [pk, increment, unique, note: 'The database table row id']
  dateCreated timestamp [default: 'now()', note: 'Date / time created']
  dateEdited timestamp [default: 'now()', note: 'Date / time last modified']
  createdBy varchar(40) [not null, default: '', note: 'The person who created the record']
  editedBy varchar(40) [not null, default: '', note: 'The person who last modified the record']

  constituentId varchar(40) [unique, not null, default: `uuid_generate_v4()`, note: 'The globally unique identifier for this constituent']
  name varchar(255) [not null]
  ein varchar(16) [unique, not null]

  indexes
  {
    constituentId
    ein
  }
}

When I export to MySql the following is generated.

CREATE TABLE `entity` (
  `id` bigint UNIQUE PRIMARY KEY AUTO_INCREMENT COMMENT 'The database table row id',
  `dateCreated` timestamp DEFAULT "now()" COMMENT 'Date / time created',
  `dateEdited` timestamp DEFAULT "now()" COMMENT 'Date / time last modified',
  `createdBy` varchar(40) NOT NULL DEFAULT "" COMMENT 'The person who created the record',
  `editedBy` varchar(40) NOT NULL DEFAULT "" COMMENT 'The person who last modified the record',
  `constituentId` varchar(40) UNIQUE NOT NULL DEFAULT (uuid_generate_v4()) COMMENT 'The globally unique identifier for this constituent',
  `name` varchar(255) NOT NULL,
  `ein` varchar(16) UNIQUE NOT NULL
);

The MySql export doesn’t like the syntax of the resulting CREATE TABLE - it doesn’t like the double quotes around the now keyword.

I also noticed that the double quotes with the DEFAULT command is also an issue. MySql seems to want single quotes.

2

Hi Omey,

Welcome to our community!

As mentioned in our dbml doc, the value wrapped inside single quotes '...' is interpreted as string type. For default value with expression type, you should wrap them inside backtick characters `…`

For MySQL 8.0 string literal, both single quotes and double quotes are acceptable. The problem is that column with type timestamp won’t accept a string as its default value.

Therefore, in your cases, replacing single quotes with backticks outside now() should make the export work:

Table entity
{
  id bigint [pk, increment, unique, note: 'The database table row id']
  dateCreated timestamp [default: `now()`, note: 'Date / time created']
  dateEdited timestamp [default: `now()`, note: 'Date / time last modified']
  createdBy varchar(40) [not null, default: '', note: 'The person who created the record']
  editedBy varchar(40) [not null, default: '', note: 'The person who last modified the record']

  constituentId varchar(40) [unique, not null, default: `uuid_generate_v4()`, note: 'The globally unique identifier for this constituent']
  name varchar(255) [not null]
  ein varchar(16) [unique, not null]

  indexes
  {
    constituentId
    ein
  }
}

I hope this will help.

Thanks