MySql CREATE TABLE and a varchar column with default value of db-function "USER"-CodePudding

With MySql.

There is a way to set a default value. I know how to do a hard-coded string.

I'm trying to use a

USER()

CURRENT_USER

CURRENT_USER()

See 'inserted_by' column below.

If I remove

DEFAULT USER()

it works ok. But that is not what I need.

I've tried all 3 above (after the word DEFAULT). I cannot figure out the magic syntax sugar.

CREATE TABLE `department` (
    `department_key` bigint NOT NULL AUTO_INCREMENT,
    `name_of` varchar(256) NOT NULL,
    `inserted_by` varchar(64) NOT NULL DEFAULT USER(),
    `create_date_off_set` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    CONSTRAINT `PK_department` PRIMARY KEY (`department_key`)
    )
    ;

References:

https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html

https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html

"My" Version:

SELECT VERSION();

8.0.23

CodePudding user response：

The default value specified in a DEFAULT clause can be a literal constant or an expression. With one exception, enclose expression default values within parentheses to distinguish them from literal constant default values.

So you want inserted_by varchar(255) NOT NULL DEFAULT (CURRENT_USER())

Unfortunately, MySQL does not allow this.

Default value expression of column 'inserted_by' contains a disallowed function: current_user.

I can't find any reason why it would be disallowed in the documentation, but that's MySQL for you.

Instead, use an insert trigger.

CREATE TRIGGER before_insert_app_users
  BEFORE INSERT ON department 
  FOR EACH ROW
  SET new.inserted_by = COALESCE(new.inserted_by, current_user())