How to set username using id in mysql

Question

I have a database table in mysql

create table userstable ( id int not null auto_increment, name varchar(80) not null, username varchar(80) not null, primary key(id) );

How to add new row in mysql database, so that username will be 'name'+('id'*100)

Example :

ID name username 1 A A100 2 B B200 3 C C300 4 user user400

You need to use triggers for that. Create a AFTER INSERT trigger for that table — Arun Krish
– Arun Krish, Commented Jul 31, 2015 at 8:54
Just to confirm what's obvious from the DDL: You are fine with duplicates in username. e.g. (1,A1,A1100) and (11,A,A1100) — vhu
– vhu, Commented Jul 31, 2015 at 9:20

Arun Krish · Accepted Answer · 2015-07-31 10:14:45Z

You need trigger for that process. Create the following trigger

CREATE TRIGGER username_change BEFORE INSERT ON userstable FOR EACH ROW BEGIN SET NEW.username = CONCAT(NEW.name,(NEW.id*100)); END

OR

INSERT INTO userstable (id,name, username) VALUES (2, 'B', CONCAT(name,(id*100)));

Try this.

Thanks for your answer,it's work, but can i solve this using subquery / query without using trigger?

Community · Accepted Answer · 2017-05-23 10:26:49Z

You'll need to write a trigger or update the field after insertion. A similar question provides more insight:

vhu · Accepted Answer · 2015-07-31 09:22:34Z

As @ArunKrish correctly pointed out, you may use TRIGGER to update the data as part of the insert. Another option is to use view:

CREATE VIEW v1 AS SELECT id,name,CONCAT(name,id*100) AS username FROM userstable;

You may also use the query as-is, without view:

SELECT id,name,CONCAT(name,id*100) AS username FROM userstable;

3 Answers 3