Automatically Initialize GETDATE() when inserting into MYSQL

~Problem: I'm inserting rows into a mysql database and I want each row to have a column where the current date(date when row was inserted) is shown.

~Attempted solution:

CREATE TABLE $this->DB.$table
              (
              id int,
              Report_Date date DEFAULT CURRENT_DATE ON UPDATE CURRENT_DATE)//this fails.

Would someone also explain what ON UPDATE means? I'm nervous that when I have multiple days of data it will update all values of Report_Date and mess up my data. The solution lies somewhere in GETDATE, I think. Furthermore I do not want time, only date. Much appreciated.

Solution

As documented under CREATE TABLE Syntax (emphasis added):

The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP or (as of MySQL 5.6.5) DATETIME column. See Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

Therefore your options are:
- use a TIMESTAMP column (since MySQL v5.6.5, you can also use a DATETIME column) and ignore the time part;
- explicitly specify the current date in the INSERT command:
```
INSERT INTO myTable (id, Report_Date) VALUES (123, CURRENT_DATE)
```
- define a BEFORE INSERT trigger:
```
CREATE TRIGGER myTrigger BEFORE INSERT ON myTable FOR EACH ROW
  SET NEW.Report_Date = IFNULL(NEW.Report_Date, CURRENT_DATE)
```
Would someone also explain what ON UPDATE means?

As documented under Automatic Initialization and Updating for TIMESTAMP and DATETIME:
- An auto-initialized column is set to the current timestamp for inserted rows that specify no value for the column.
- An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values. To prevent an auto-updated column from updating when other columns change, explicitly set it to its current value. To update an auto-updated column even when other columns do not change, explicitly set it to the value it should have (for example, set it to CURRENT_TIMESTAMP).