SQL中为列配置更新时自动记录当前时间报错求助

Hey there, let's break down what's going wrong here and how to fix it:

1. Why the "unexpected 'CHANGE'" error happens #

The CHANGE syntax you're using is specific to MySQL. But based on the "SQL compilation error" message, it looks like you're working with a database that doesn't recognize this keyword—most likely Snowflake, which uses different syntax for modifying column properties.

2. Correct Syntax for Snowflake (to set up the column) #

First, let's fix the column definition with Snowflake's supported ALTER COLUMN syntax:

ALTER TABLE SPRD_MGMT_INP_INDEX_CHANGE_RATE 
ALTER COLUMN "UPLOAD TIME" SET DATA TYPE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

This sets the column to a timestamp type, enforces non-null values, and auto-fills the current timestamp when a new row is inserted.

3. Adding Auto-Update on Row Changes (Snowflake doesn't support ON UPDATE) #

Snowflake doesn't have MySQL's ON UPDATE CURRENT_TIMESTAMP built-in feature. To automatically update the timestamp whenever a row is modified, you'll need to create a trigger:

CREATE OR REPLACE TRIGGER UPDATE_UPLOAD_TIME
BEFORE UPDATE ON SPRD_MGMT_INP_INDEX_CHANGE_RATE
FOR EACH ROW
BEGIN
  SET NEW."UPLOAD TIME" = CURRENT_TIMESTAMP();
END;

This trigger runs every time a row is updated, overwriting the UPLOAD TIME value with the current timestamp.

If You're Actually Using MySQL... #

If you're working with MySQL and still seeing this error, the issue is likely the quote style. MySQL uses backticks (`) for identifiers by default (unless you enable ANSI_QUOTES). Try this instead:

ALTER TABLE SPRD_MGMT_INP_INDEX_CHANGE_RATE CHANGE `UPLOAD TIME` `UPLOAD TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

内容的提问来源于stack exchange，提问作者Karab