How to Add integer column to an String column in MySQl 5.0

Question

I Want to add an Integer Column to a String that's because i need to generate a varchar variable with a numeric part that automatically increments. For example, P000001,P000002...

In order to do that what i am doing while creation of table i have taken an int field ID which auto_increments and i am Concatenating P with 00000 and the ID value

The Table i have created is :

CREATE TABLE tblAcceptTest(
ID int AUTO_INCREMENT NOT NULL primary key,
PatientID as CONCAT('P' , CONCAT('000000',CAST(ID as char)))
);

It Shows me the error from as keyword. Please help

MySQL doesn't supported computed columns in table specifications. See this question for info and possible workarounds: [Column calculated from another column?](http://stackoverflow.com/questions/5222044/column-calculated-from-another-column) — Dan J, Mar 27 '13 at 19:57

score 1 · Answer 1 · answered Mar 27 '13 at 20:00

MySQL's documentation (http://dev.mysql.com/doc/refman/5.1/en/create-table.html) says, "the default value must be a constant; it cannot be a function or an expression." Why don't you just get the PatientID value afterward as part of the SELECT:

SELECT CONCAT('P', LPAD(ID, 6, 0)) AS PatientID FROM tblAcceptTest;

score 0 · Answer 2 · answered Mar 27 '13 at 19:55

0

It looks like you want six digits after the "P", so try this for your expression:

CONCAT('P', LPAD(ID, 6, '0'))

answered Mar 27 '13 at 19:55

Ed Gibbs

25,924
4
46
69

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL serv er version for the right syntax to use near 'as CONCAT('P', LPAD(ID, 6, '0')) )' at line 3 – user2200561 Mar 27 '13 at 20:00
user2200561 - sorry, MySQL doesn't support virtual/computed columns. I should have been more clear: my answer is how you create the value you want ("P" followed by ID left-padded with zeros to 6 places). You can use it when you query, or you can use the workaround DanJ references in the comments above, or you can incorporate it into a view. But you can't use it for a computed column because there's no such thing in MySQL. – Ed Gibbs Mar 27 '13 at 20:08
Sounds good! If you decide to try the workaround involving triggers please let me know if you have any questions; I'll try to help. – Ed Gibbs Mar 27 '13 at 20:28

score 0 · Answer 3 · answered Mar 27 '13 at 21:05

Mysql has little support for computed columns. Patient ID from your specification could be a char(7)

CREATE TABLE tblAcceptTest(
    ID int AUTO_INCREMENT NOT NULL primary key,
    PatientID char(7)
);

Then create some triggers. Note that the following insert trigger will cause issues with high concurrency servers.

DELIMITER |

CREATE TRIGGER tblAcceptTest_insert BEFORE INSERT ON tblAcceptTest
  FOR EACH ROW BEGIN
    DECLARE next_id INT;
       SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tblAcceptTest');
    SET NEW.PatientID =  CONCAT('P' , RIGHT(CONCAT('000000',next_id),6)) ;
  END;
|

CREATE TRIGGER tblAcceptTest_update BEFORE UPDATE ON tblAcceptTest
  FOR EACH ROW BEGIN
     SET NEW.PatientID =  CONCAT('P' , RIGHT(CONCAT('000000',NEW.ID),6)) ;
  END;
|

DELIMITER ;

You use relationships and views to achieve the same result.

CREATE TABLE `patient` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `patient` varchar(60) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `accepted_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `patient_id` int(11) NOT NULL,
  `accepted` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `patient_id` (`patient_id`),
  CONSTRAINT `accepted_test_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patient` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create or replace view accepted_test_veiw as 
select CONCAT('P' , RIGHT(CONCAT('000000',patient_id),6)) patient_key
, accepted 
, id accepted_test_id
, patient_id
from accepted_test ;

select * from `accepted_test_veiw`

How to Add integer column to an String column in MySQl 5.0

3 Answers3