Trouble with Generated Column
Not sure if this is correct forum for this issue.
I'm trying to migrate a Microsoft Access back-end database to MySQL, and having problems with generated columns. Couldn't get migration assistant to work properly, so used a 3rd-party tool to migrate my tables. This created the tables but I have to fix some things before populating data.
In Access I have some calculated columns. For example, the 'Heading' column is a combination of title (Mr & Mrs, etc.) and names and spouse names.
Here is calculation in Access to build a heading:
IIf([UseTitle],[Title] & " ","") & IIf(IsNull([DonorFirstName]),[DonorLastName],[DonorFirstName] & IIf(IsNull([SpouseName])," " & [DonorLastName],IIf(IsNull([SpouseLastName])," and " & [SpouseName] & " " & [DonorLastName]," " & [DonorLastName] & " and " & [SpouseName] & " " & [SpouseLastName])))
(In Access, IIF is same as MySQL IF function)
So if I'm supposed to use a title, put that in.
If first name is null, add last name (that's how this database denotes a company name). Otherwise, add first name. If no spouse name, add space and last name. If spouse, add " and " plus spouse first name. Then add last name. So now you have "Mr. & Mrs. John and Jane Doe". In case the spouse has a different last name, it will instead build a string like "Mr. & Mrs. John Doe and Jane Smith".
I tried doing this in MySQL generated column, but it keeps erroring out. It seems to work, then when I go into the table designer in Workbench, it says Error parsing DDL. When I view the DDL, the calculated column shows the error on the Heading column:
`Heading` varchar(509) CHARACTER SET utf8 COLLATE utf8_general_ci GENERATED ALWAYS AS (concat(if(((`UseTitle` = 0) or (`Title` is null)),_utf8mb3'',concat(`Title`,_utf8mb3' ')),`DonorFirstName`,_utf8mb3' ',`DonorLastName`)) VIRTUAL,
The squiggly red line is at _utf8mb3'', right under the ''. It says "Syntax error: extraneous input found - expected 'comma'".
This is using a very simple generated field for a test, trying to combine only 2 or 3 columns.
It's very strange that the DDL will create a new table, then give me a syntax error when I try to open the table designer in Workbench.
Is there a better way to accomplish this rather than a bunch of contats with IF statements like I'm doing?