时间戳

首页 > 解决方案 > SQL query with ALTER TABLE print error #1064 in query execution

mysql - SQL query with ALTER TABLE print error #1064 in query execution

问题描述

I've built an SQL query in Aruba MySQL database using the alter table Statement to generate a new column with comma-separated values. I'm trying to automate the concatenate function every time a new record is submitted into the database table. After I execute the query I always get a #1064 error.

ALTER TABLE 1459630_form_1
    ADD COLUMN 'datimarketing' 
        MEDIUMTEXT GENERATED ALWAYS AS (CONCAT(`segmento_auto_richiesto`,',',`alimentazione`,',',`km_annui_percorsi`,',',`modalit_di_acquisto`,',',`budget_di_spesa`,',',`rata_mensile`,',',`abitudini_di_acquisto`,',',`numero_di_componenti_del_nucleo_familiare`,',',`hobby`,',',`professione`,',',`iscritto_ad_associazioni_di_categoria`,',',`privacy_mkt_all`,',',`giorno_preferito_per_il_ricontatto`,',',`orario_preferito_per_il_ricontatto`)) STORED AFTER `orario_preferito_per_il_ricontatto`
        UPDATE TABLE(1459630_form_1)

Somebody could help me? Thanks

标签： mysqlsqlalter-tablegenerated-columns

解决方案

In MySQL the syntax goes like:

ALTER TABLE 1459630_form_1
    ADD COLUMN datimarketing MEDIUMTEXT 
    GENERATED ALWAYS AS (CONCAT_WS(',', `segmento_auto_richiesto`, `alimentazione`, `km_annui_percorsi`)) STORED 
    AFTER `orario_preferito_per_il_ricontatto`
;

I shorten the number of concatenated columns to make the code more readable (but the logic is the same of course).

Rationale:

  • the column name should not be surrounded with single quotes

  • parentheses need to balance

  • UPDATE TABLE() is not applicable here

Side note: CONCAT_WS() comes handy to shorten the CONCAT() expression.

推荐阅读