How to concat_ws primarykey(id) and date in a new column on same table?(MySQL)

17
January 12, 2019, at 03:00 AM

I need add a new column named date_id by using concat_ws('-', curdate,id) on a same table, the id is a primarykey and auto_increment. How to do this?

I tried add a column and updated it. it worked, but when I insert some new row, the error showed Filed 'id' doesnot have a default value. How to solve this?

ALTER TABLE table1 ADD date_id VARCHAR(50);
UPDATE table1 SET date_id = CONCAT_WS('-',date_format(CURDATE(),'%Y%m%d'),id);

update question

I have created a table:

USE table_name;
CREATE TABLE IF NOT EXISTS `gzsrieQA`(
   `id` INT(3) UNSIGNED ZEROFILL AUTO_INCREMENT,
   `record_id` VARCHAR(50),
   `date` date,
   `pro_name` VARCHAR(50),
   `error_info` VARCHAR(255),
   `error_describe` VARCHAR(255),
   `reason` VARCHAR(255),
   `solution` VARCHAR(255),
   `solution_file` BLOB,
     `solution_file_name` VARCHAR(50),
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER table_name AUTO_INCREMENT = 001;
UPDATE table_name SET record_id = CONCAT_WS('-','SRIE-DSJ-QA',date_format(CURDATE(),'%Y%m%d'),id);

When I insert into some new data, the result showed 'record_id' is null. it means I need a trigger? But the question is if I insert lots of data, the trigger is too slow.Is any solution to solve this if I don't use trigger?
For example:

|id|record_id|name|
|-----|-----|-----|
|001| |ABC|
|002| |xyz|
# I want to get:
|id|record_id|name|
|-----|-----|-----|
|001|ABC001|ABC|
|002|xyz002|xyz|
# and when I insert into name=AAA, the result is:
|id|record_id|name|
|-----|-----|-----|
|001|ABC001|ABC|
|002|xyz002|xyz|
|003|aaa003|AAA|

THANKS!

Answer 1

I tried MySQL and it allowed your statement however you can set a default and then remove it.

select version();
| version() |
| :-------- |
| 8.0.13    |
create table table1 (id int auto_increment primary key);
insert into table1(id) values (NULL),(NULL),(NULL),(NULL)
ALTER TABLE table1 ADD date_id VARCHAR(50)  default '';
UPDATE table1 SET date_id = CONCAT_WS('-',date_format(CURDATE(),'%Y%m%d'),id);
select * from table1
id | date_id   
-: | :---------
 1 | 20190111-1
 2 | 20190111-2
 3 | 20190111-3
 4 | 20190111-4
show create table table1
Table  | Create Table                                                                                                                                                                                                            
:----- | :-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
table1 | CREATE TABLE `table1` (<br>  `id` int(11) NOT NULL AUTO_INCREMENT,<br>  `date_id` varchar(50) DEFAULT '',<br>  PRIMARY KEY (`id`)<br>) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
alter table table1 alter column date_id DROP DEFAULT, ALGORITHM=INPLACE, LOCK=NONE;
show create table table1;
Table  | Create Table                                                                                                                                                                                                 
:----- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
table1 | CREATE TABLE `table1` (<br>  `id` int(11) NOT NULL AUTO_INCREMENT,<br>  `date_id` varchar(50),<br>  PRIMARY KEY (`id`)<br>) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

db<>fiddle here

READ ALSO
mysqldump fails with &ldquo;Skipping dump data for table &#39;table1&#39;, it has no fields&rdquo;

mysqldump fails with “Skipping dump data for table 'table1', it has no fields”

I'm running mysqldump from an older mysql databaseThe mysqldump is part of a mariadb distribution if it matters

14
Access denied for user &#39;root&#39;@&#39;localhost&#39; Laravel / debian 18

Access denied for user 'root'@'localhost' Laravel / debian 18

I have a problem with laravel and mysqlIt is possible for me to php artisan migrate and seed my database

33
Getting non duplicate results from bridging table

Getting non duplicate results from bridging table

I have three tables, contact, contact_category and contact_categories

30
PHP While Statement Delete only one row from table Mysql PDO

PHP While Statement Delete only one row from table Mysql PDO

I have a while statement and a PDO statement to delete one row if someone clicks the delete button

7