MySQL8. 0 learning record 08 - data type default

How to define default values

Type defaults can be defined using literals and expressions , The expression needs to be enclosed in parentheses ：

create table t_text(
	a int default 0,
	b varchar(64) DEFAULT (UUID()),
	c DATE DEFAULT (CURRENT_DATE + INTERVAL 1 YEAR),
	d float default (RAND() * RAND()),
	e DATETIME default CURRENT_TIMESTAMP  ON UPDATE CURRENT_TIMESTAMP,
	f BLOB default ('hello')
);

insert into t_text() values();

SELECT * FROM t_text;

a|b                                   |c         |d       |e                  |f    |
-+------------------------------------+----------+--------+-------------------+-----+
0|66f735d9-c17c-11ec-89b9-0242ac120003|2023-04-21|0.496599|2022-04-21 14:07:38|hello|

The date type has a property that can be updated automatically ON UPDATE, When updating other column values , The column is automatically updated

update t_text set a = 1 where a=0;
SELECT * FROM t_text;

You can see the time e Updated ：

a|b                                   |c         |d       |e                  |f    |
-+------------------------------------+----------+--------+-------------------+-----+
1|66f735d9-c17c-11ec-89b9-0242ac120003|2023-04-21|0.496599|2022-04-21 14:08:23|hello|

Expression requirements

• Literally 、 Built in functions and operators
• Cannot use subquery 、 Parameters 、 Variable 、 Storage function
• Column cannot have AUTO_INCREMENT attribute
• Expressions can reference other columns , But if you reference the generated column or the column with the default value of the expression , The referenced column must be defined first

The general list expression refers to other columns in an unlimited order ：

create table t_text(
  a int default 0,
  b int DEFAULT (a + 2),
  c varchar(10) default(concat(d,'_c')),
  d varchar(10) default 'd'
);

insert into t_text() values();
/* a|b|c |d| 0|2|d_c|d| */
insert into t_text(a,d) values(100,'hello');
/* a |b |c |d | 100|102|hello_c|hello| */

as follows ,d The default value of is the expression ,c stay d The previous definition ,c Expression reference for d It's illegal ：

create table t_text(
	a int default (0),
	b int default (a+10), -- correct 
	c varchar(10) default(concat(d,'_c')), --  error 
	d varchar(10) default ('d')
);

in addition ：BLOB, TEXT, GEOMETRY and JSON You can only have default values with expressions

Implicit default processing

If not explicitly specified DEFAULT, And the type can be NULL, that MySQL It will also add DEFAULT NULL.

For definition NOT NULL The column of , If in INSERT、REPLACE、UPDATE In the sentence , Assign a value of... To this column NULL, So according to SQL The mode is processed accordingly ：

• If it's a strict model , Will report a mistake , And roll back the transaction
• If it's a non strict model , Then the default value is determined according to the data type :

create table t_text(
   a int not null,
   b varchar(10) not null,
   c decimal(4,2) not null,
   d datetime not null,
   e BLOB not null,
   f JSON not null
);

SET sql_mode = '';
insert into t_text() values();

give the result as follows ：

a|b|c   |d                  |e|f   |
-+-+----+-------------------+-+----+
0| |0.00|0000-00-00 00:00:00| |null|

Note here f Not at all MySQL Of NULL type , It is JSON Of NULL.