How to set default year from current timestamp in phpMyAdmin page without SQL query command manually-CodePudding

I want to store scholarship database for subscribe the students that interest in each scholarship. But can I set the year from current_timestamp() in phpMyAdmin page without typing the SQL command manually.

The table definiton contains.

ScholarshipID int primary auto_increment
Title varchar(250)
Paragraph varchar(5000)
Year year(4) default current_timestamp()
CreatedAt datetime default current_timestamp()
UpdatedAt datetime default current_timestamp() on update current_timestamp()

I'm found out that Year row values is inserted as string.

And the result if I save the inserted data.

The SQL query that I expected to be.

INSERT INTO `scholarship_program` (`ScholarshipID`, `Title`, `Paragraph`, `Year`, `CreatedAt`, `UpdatedAt`) VALUES (NULL, 'หัวข้อข่าวทุนการศึกษา', 'เนื้อหาข่าวทุนการศึกษา\r\n- ใจความ\r\n- เนื้อหาละเอียด\r\n- สรุปเนื้อหา\r\nทิ้งท้าย', YEAR(current_timestamp()), current_timestamp(), current_timestamp());

The SQL query result from phpMyAdmin.

INSERT INTO `scholarship_program` (`ScholarshipID`, `Title`, `Paragraph`, `Year`, `CreatedAt`, `UpdatedAt`) VALUES (NULL, 'หัวข้อข่าวทุนการศึกษา', 'เนื้อหาข่าวทุนการศึกษา\r\n- ใจความ\r\n- เนื้อหาละเอียด\r\n- สรุปเนื้อหา\r\nทิ้งท้าย', 'current_timestamp()', current_timestamp(), current_timestamp());

I'm tried to use function YEAR but it has an error. Because it treated current_timestamp() as string.

SQL Query.

INSERT INTO `scholarship_program` (`ScholarshipID`, `Title`, `Paragraph`, `Year`, `CreatedAt`, `UpdatedAt`) VALUES (NULL, 'Scholarship News Header.', 'Scholarship News Paragraph.', YEAR('current_timestamp()'), current_timestamp(), current_timestamp())

SQL Said. #1048 - Column 'Year' cannot be null

CodePudding user response：

You could instead use a TRIGGER to supply a default value for the "Year" column.

Alter the "Year" column data type.

ALTER TABLE scholarship_program MODIFY Year YEAR NULL;

Set up a trigger ("BEFORE INSERT").

DROP TRIGGER IF EXISTS `scholarship_program_insert_default_year`;

DELIMITER $$
CREATE TRIGGER scholarship_program_insert_default_year
BEFORE INSERT ON scholarship_program FOR EACH ROW 
BEGIN
    SET NEW.Year = TRIM(NEW.Year);
    
    IF (NEW.Year IS NULL) OR (NEW.Year = '') THEN
        SET NEW.Year=YEAR(CURRENT_TIMESTAMP);
    END IF;
END $$
DELIMITER ;

To insert a new record, you can now pass a NULL value for the "Year" column.

INSERT INTO `scholarship_program` (`ScholarshipID`, `Title`, `Paragraph`, `Year`, `CreatedAt`, `UpdatedAt`) VALUES (NULL, 'Bsc in computer science', 'Learning about PCs...', NULL, current_timestamp(), current_timestamp());

Your "PHPMyAdmin" application will also work fine without errors.