Moh-Aw Moh-Aw - 5 months ago 23
MySQL Question

Convert default column value from datetime to varchar

I have to add a varchar column to a table which defaults to the current timestamp. To do this I somehow need to convert the value from datetime to varchar.

I tried the following

ALTER TABLE `TableName`
CHANGE COLUMN `DocumentID` `DocumentID` VARCHAR(150) NULL DEFAULT CONVERT(NOW(), CHAR);


or

ALTER TABLE `TableName`
CHANGE COLUMN `DocumentID` `DocumentID` VARCHAR(150) NULL DEFAULT CONVERT(CURRENT_TIMESTAMP, CHAR);


I always get an error message, that my syntax is wrong. I am using MariaDB and HeidiSQL. Is there a way to do this?

EDIT: I am basically looking for a MySQL equivalent of the following SQL Server statement:

ALTER TABLE [dbo].[TableName] ADD CONSTRAINT [DF_TableName_DocumentID] DEFAULT (getdate()) FOR [DocumentID]

Answer

MariaDB document says

In MariaDB 10.2.1 you can use most functions in DEFAULT. Expressions should have parentheses around them.

Hence you may check for the version of MariaDB and use the right syntax (parenthesis around expression):

 (CONVERT(CURRENT_TIMESTAMP, CHAR))

Update

As an alternative, you may use Trigger to set the function value for the stable releases (< 10.2)