Question 205314 in Mysql Question posted in
The official MySQL documentation can be found here.

How to declare pre-Computed Columns on MySql?

I was a student of Oracle SQL where i used to use "AS" keyword for certain columns to automatically fill the values to selected columns based on mathematical computations. If you see in the example below:

CREATE TABLE CHIT_CHAT_BILING
(
    BILL_NO NUMBER(3) NOT NULL,
    CHAT_NAME VARCHAR2(20) NOT NULL,
    PRICE NUMBER(3),
    PURCHASE_TIME TIMESTAMP NOT NULL,
    SERVICE_TAX AS (PRICE*(12.5/100)),
    VAT AS (PRICE*(4.5/100))
);

Values of SERVICE_TAX field & VAT field is computed from PRICE field. The above DDL command was working fine in Oracle SQL, but not in MySQL database.

What is the alternative? How to achieve this in MySQL database? Please guide me

IN MySql AS keyword is not working.

Tags:

3

Answers


  1. 0

    The problem is not with the AS keyword or syntax for the calculation. You need to use data-types that MySQL supports and set the data-type for the generated columns.

    In MySQL 8.0:

    CREATE TABLE CHIT_CHAT_BILING (
  BILL_NO       DECIMAL(3, 0) NOT NULL,
  CHAT_NAME     VARCHAR(20) NOT NULL,
  PRICE         DECIMAL(3, 0),
  PURCHASE_TIME TIMESTAMP NOT NULL,
  SERVICE_TAX   DECIMAL(5, 2) AS (PRICE*12.5/100) ,
  VAT           DECIMAL(5, 2) AS (PRICE*4.5/100)
);

    fiddle

    Login or Signup to reply.
  2. 0

    Your problem is not the computed columns. You have to change the data types.

    CREATE TABLE CHIT_CHAT_BILING
(
    BILL_NO int(3) NOT NULL,
    CHAT_NAME VARCHAR(20) NOT NULL,
    PRICE int(3),
    PURCHASE_TIME TIMESTAMP NOT NULL,
    SERVICE_TAX double AS (PRICE*(12.5/100)),
    VAT double AS (PRICE*(4.5/100))
);

    https://dbfiddle.uk/76dfOd3l

    Login or Signup to reply.
  3. 0

    The problem is with the datatypes.

    • There is no VARCHAR2 in MySQL. Use VARCHAR instead.
    • There is no NUMBER in MySQL. Use NUMERIC or DECIMAL (or any other appropriate numeric data type) instead.
    • Generated columns must have a data type, too.

    The corrected statement:

    CREATE TABLE CHIT_CHAT_BILING 
( 
  BILL_NO         NUMERIC(3)     NOT NULL,
  CHAT_NAME       VARCHAR(20)    NOT NULL,
  PRICE           NUMERIC(3),
  PURCHASE_TIME   TIMESTAMP      NOT NULL,
  SERVICE_TAX     NUMERIC(6,3)  AS (PRICE*(12.5/100)),
  VAT             NUMERIC(6,3)  AS (PRICE*(4.5/100))
);

    Demo: https://dbfiddle.uk/HKO9EHXo

    More infos here:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search