Column default-metadata storage in MySQL 8
Posted by: Martijn Tonies
Date: November 20, 2018 07:45AM

Hi,

This is valid SQL:
CREATE TABLE default_test (
int_col Integer(11) DEFAULT 44,
bi_col_exp BigInt(20) DEFAULT (8 * 8),
dt_col DateTime DEFAULT CURRENT_TIMESTAMP,
d_col Date DEFAULT (curdate()),
t_col Time DEFAULT (curtime()),
d_col_exp Date DEFAULT (curdate() + 8),
enum_col Enum('value1', 'value2', 'value3') DEFAULT 'value1',
vc_col VarChar(20) DEFAULT 'test',
vc_col_exp VarChar(100) DEFAULT (concat('test','test'))
) ENGINE=InnoDB

Now, I want to check the defaults in the meta data, I can either run:
show full columns from default_test
or
select table_name, column_name, COLUMN_DEFAULT
from `COLUMNS` where table_name = 'default_test'

This results in the following DEFAULTs:

int_col > 44
bi_col_exp > (8 * 8)
dt_col > current_timestamp
d_col > curdate()
t_col > curtime()
d_col_exp > (curdate() + 8)
enum_col > value1
vc_col > test
vc_col_exp > concat(_utf8mb3\'test\',_utf8mb3\'test\')

Perhaps I'm doing something wrong, but from these results, it's very unclear what default is supposed to go between (..), what default is supposed to go between '..' and what default is to be copied 'as is'.

Not even for character columns you can't simply use '..', cause if it's an expression instead of a literal, it should be in (..).

And how about the concat expression, that's even worse.

Yes, a SHOW CREATE TABLE shows almost what's to be done, except the bi_col_exp gets an extra set of parenthesis. But without this, how should you know from the meta data tables/commands how this DDL is to be re-created?

Options: ReplyQuote


Subject
Views
Written By
Posted
Column default-metadata storage in MySQL 8
191
November 20, 2018 07:45AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.