MySQL: How to set the default string using another field by string-interpolation?

All we need is an easy explanation of the problem, so here it is.

I have the following table and I wondering whether string interpolation using another is possible:

CREATE TABLE Account (
    AccountID varchar(25),
    About varchar(300) NOT NULL DEFAULT
        'My id is ${AccountID}',
    PageLink varchar(255) NOT NULL UNIQUE,

That is: I want different default values for each row using the primary key.

id: 1
About: My id is 1

id: 2
About: My id is 2

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This is possible in MySQL 8.0 because they support expressions in DEFAULT clauses now. But it won’t work with interpolation like you show. It works like any other MySQL string expression:

CREATE TABLE Account (
 AccountID varchar(25),
 About varchar(300) not null default (CONCAT('My id is ', AccountID)),
 Pagelink varchar(255) not null unique
);

Example:

mysql> INSERT INTO Account SET AccountID = '8675309', Pagelink = '<link>';
Query OK, 1 row affected (0.00 sec)

mysql> select * from Account;
+-----------+------------------+----------+
| AccountID | About            | Pagelink |
+-----------+------------------+----------+
| 8675309   | My id is 8675309 | <link>   |
+-----------+------------------+----------+

In earlier versions of MySQL, defaults can only be a literal value, or NULL, or CURRENT_TIMESTAMP, but not an expression.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply