how to set default value for text type in mysql

mysql, sql

Solution

I have changed not null to null for about us field

CREATE TABLE IF NOT EXISTS `te` (
  `id` int(30) NOT NULL,
  `name` text NOT NULL,
  `address` text NOT NULL,
  `Aboutus` text NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Here is your trigger `BEFORE INSERT`

CREATE TRIGGER new_insert
BEFORE INSERT ON `te`
FOR EACH ROW 
SET NEW.`Aboutus` = CASE WHEN NEW.Aboutus IS NULL THEN 'Not Updated' ELSE NEW.Aboutus END
;

Insert without `Aboutus`

INSERT INTO `te` (`id`, `name`, `address`) 
VALUES (1, 'name', 'address') ;

Insert with `Aboutus`

INSERT INTO `te` (`id`, `name`, `address`, `Aboutus`) 
VALUES (2, 'name', 'address', 'Aboutus') ;

Insert by passing null `Aboutus`

INSERT INTO `te` (`id`, `name`, `address`, `Aboutus`) 
VALUES (3, 'name', 'address', null) ;

Demo

Edit As @garethD pointed a case for update scenario,you also need another trigger on `BEFORE UPDATE` so if null appears in update then aboutus should be updated as `Not Updated`

CREATE TRIGGER update_trigger
BEFORE UPDATE ON `te`
FOR EACH ROW 
SET NEW.`Aboutus` = CASE WHEN NEW.Aboutus IS NULL THEN 'Not Updated' ELSE NEW.Aboutus END
;

UPDATE te
SET AboutUs = NULL;

Demo 2

Problem

``` CREATE TABLE IF NOT EXISTS `te` ( `id` int(30) NOT NULL, `name` text NOT NULL, `address` text NOT NULL, `Aboutus` text NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ``` Here Table 'te' consist 4 fields id, name, address, Aboutus, Aboutus is optional means how can i update default text to the Profile in db , by phpmyadmin sql

Original source

Related problems