CODEWITHSUNDEEP
mysql
Malork
Malork

Reputation: 276

Can't add a Generated Column

I have a Price column (DECIMAL 6,2) in my MySQL Products MyISAM table and I'm trying to add a Generated Column to multiply it per 2. I'm trying to use this:

ALTER TABLE Produtos ADD COLUMN Price_double DECIMAL(6,2) AS (Price*2);

But I'm getting this in HeidiSQL:

Error SQL (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS (Preco*2)' at line 1

I don't know what to do. Really...

My MySQL's version is 5.5.51-38.2

Upvotes: 1

Views: 3172

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

MySQL only supports generated columns in version 5.7 or later, q.v. the documentation:

As of MySQL 5.7.6, CREATE TABLE supports the specification of generated columns. Values of a generated column are computed from an expression included in the column definition.

However, you syntax appears to be correct, and if you were running 5.7 or later you should not see any error.

For a workaround to using a computed column, you can just compute the product in the SELECT statement in your PHP code. Keep in mind that there is overhead to having any column in your table, including a computed one. And if you are not sure that you will be needing the computed amount frequently, you could argue against altering your table in the first place.

Upvotes: 2

Related Questions