Converting SQL Server code to MySQL

Question

I am trying to concatenate two integers as the default value in a third field. My create table in SQL Server works fine:

CREATE TABLE MEI_Tbl
(
MEI_ID int PRIMARY KEY IDENTITY (1,1),
SRC tinyint NOT NULL DEFAULT '2',
HEI_ID AS (Cast (SRC as varchar)+ Cast (MEI_ID as varchar))
);

but when I try to create it in MySQL, I cannot find the equivalent for the concatenation of the two integers (Line 5 HEI_ID...). ** I am aware of changing IDENTITY (1,1) to AUTO_INCREMENT for MySQL. ** I have also tried several concat methods, but to no avail.

MySQL seems happier if I define the datatype for HEI_ID, and I have done so as varchar and int but again no success.

I have spent too much time reading about tool kits to convert SQL Server to MySQL. I just want to create the table in MySQL.

Any input would be appreciated.

Gordon Linoff · Accepted Answer · 2017-03-06 00:43:09Z

1

MySQL does not support computed columns. Instead, you can use a view:

CREATE TABLE MEI_Tbl (
    MEI_ID int PRIMARY KEY AUTO_INCREMENT,
    SRC tinyint NOT NULL DEFAULT 2
);

CREATE VIEW v_MEI_Tbl as
    SELECT MEI_ID, SRC,
           CONCAT(src, mei_d) as HEI_ID 
    FROM MEI_Tbl
);

Then query from the view.

answered Mar 6, 2017 at 0:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amateur Over a year ago

Thank you, sir! Your first sentence ("MySQL does not support computed columns.") is not something I had read. Nor did I come across it in my research. Your suggestion for a workaround also makes sense.

Collectives™ on Stack Overflow

Converting SQL Server code to MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related