Can MySQL Columns Use Functions as Default Values?

Using Functions as Default Values in MySQL

In MySQL, it is not possible to specify a function as the default value for a column. This can be frustrating, especially if you want to use a function to generate a unique value for a column, such as a UUID.

Error Message

When you attempt to create a table with a default value that is a function, you will receive an error message similar to the following:

ERROR 1064 (42000): 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 'uuid()' at line 10

Alternative Solution

While you cannot use a function directly as a default value, there is a workaround that involves using a trigger. A trigger is a stored procedure that is automatically executed when a specific event occurs, such as the insertion of a new row into a table. Here is an example of a trigger that can be used to generate a UUID for the api_key column in the app_users table:

CREATE TRIGGER before_insert_app_users
BEFORE INSERT ON app_users
FOR EACH ROW
SET new.api_key = uuid();

This trigger will execute before any new rows are inserted into the app_users table. For each row, it will set the value of the api_key column to a UUID. This ensures that every new row has a unique api_key value.

The above is the detailed content of Can MySQL Columns Use Functions as Default Values?. For more information, please follow other related articles on the PHP Chinese website!