how to set default value for text type in mysql

Question

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

web page but is there any way to do default text for that field like as define for integers @Ullas — papa.ramu, May 27 '14 at 10:04
That means if the user doesn't fill that field, the default text should update in the database rite? — Ullas, May 27 '14 at 10:08
No **BLOB/TEXT columns can't have a default value** you may need to create a trigger — M Khalid Junaid, May 27 '14 at 10:09
Yep just as @MKhalidJunaid said. Found it http://stackoverflow.com/questions/3466872/why-cant-a-text-column-have-a-default-value-in-mysql — asprin, May 27 '14 at 10:10

score 10 · Accepted Answer · edited May 23 '17 at 12:29

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

+1, It will also need a trigger to handle updates, to stop someone running [`UPDATE te SET AboutUs = NULL;`](http://sqlfiddle.com/#!2/0aec4/1) — GarethD, May 27 '14 at 10:44
@GarethD thanks for pointing on such a mistake i haven't really thought regarding update scenario thanks again — M Khalid Junaid, May 27 '14 at 10:52

score 0 · Answer 2 · answered Aug 10 '17 at 11:46

For Ubuntu 16.04:

How to disable strict mode in MySQL 5.7:

Edit file /etc/mysql/mysql.conf.d/mysqld.cnf

If below line exists in mysql.cnf

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Then Replace it with

sql_mode='MYSQL40'

Otherwise

Just add below line in mysqld.cnf

sql_mode='MYSQL40'

This resolved problem.

how to set default value for text type in mysql

2 Answers2

Demo

Demo 2

Linked

Related