MySQL Set default field value concat with record id

Question

I have MySQL table with the following structure:

-------------
| id | name | 
-------------

Where ID is int auto_increment and name is just varchar(255).

So I need to set using database default value for the name (if it's empty after insert request) like:

INSERT INTO users;

id = 1
name = "User1"

... it should also work with null:

INSERT INTO users (name) VALUES (null);

id=8232
name = "User8232"

and also it would be nice if we send empty value for name:

INSERT INTO users (name) VALUES ('');

id=8233
name = "User8233"

But if I explicitly set value that should be:

INSERT INTO users VALUES ('TestUser')

id=8232
name = "TestUser"

Also, it would be nice if that logic would be not in the trigger but in the table definition or in the insert query itself.

So the purpose of that making unique User name for new registrations.

Thank you!

@Ben The unique user name: like "User" + ID (updated also question description) — xercool, Apr 17 '20 at 09:49
The default for the list of columns is the entire set. Hence your last `INSERT` is invalid. — Rick James, Jun 30 '20 at 19:23

score 1 · Answer 1 · answered Apr 17 '20 at 10:40

1

you can use the stored procedure to achieve this. create a stored procedure which accepts the parameter of UserName. In your SP, check if the UserName is null or empty, write your logic. Let me know if you need exact SP defination

answered Apr 17 '20 at 10:40

Ankur Munjaal

87
5

MySQL Set default field value concat with record id

1 Answers1