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


This is valid SQL:
CREATE TABLE default_test (
int_col Integer(11) DEFAULT 44,
bi_col_exp BigInt(20) DEFAULT (8 * 8),
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'))

Now, I want to check the defaults in the meta data, I can either run:
show full columns from default_test
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

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

Sorry, you can't reply to this topic. It has been closed.

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.