How to implement MySQL's default constraint default and zero-fill constraint zerofill

Default constraints

MySQL default value constraints are used to specify the default value of a column.

Add default constraints

• Method1: Field name> DataClassType> default Defaultvalue>;

• method2: alter table Table name modify Column name ClassType default Defaultvalue;

create table t_user10 (
id int ,
name varchar(20) ,
address varchar(20) default ‘北京' -- 指定默认约束
);

-- alter table 表名 modify 列名 类型 default 默认值;
create table t_user11 (
id int ,
name varchar(20) ,
address varchar(20)
);
alter table t_user11 modify address varchar(20) default ‘北京';

Delete the default constraint

-- alter table <表名> modify column <字段名> <类型> default null;
alter table t_user11 modify column address varchar(20) default null;

Zero padding constraint

1. When inserting data, when the length of the value of the field is less than the defined length, The corresponding 0
2 will be added in front of the value. The zerofill default is int(10)
3. When using zerofill, the unsigned (unsigned) attribute will be automatically added by default. After using the unsigned attribute, the value The range is 2 times the original value, for example, signed is -128~127, unsigned is 0~256.

For example: Now set the length of a certain field to 5, then the real data is 1, then what is displayed in your database is 00001;

create table t_user12 (
id int zerofill , -- 零填充约束
name varchar(20)
);

alter table t_user12 modify id int;

-- 1. 添加约束
create table t_user12 (
id int zerofill , -- 零填充约束
name varchar(20)
);
insert into t_user12 values(123, '张三');
insert into t_user12 values(1, '李四');
insert into t_user12 values(2, '王五');

Summary

We have learned a lot of constraints before. Here is a rough summary. We need to set these constraints according to our own application scenarios, so that we can finally meet our daily needs:

-- 总结
-- 1：通过修改表结构添加主键约束

create table emp4(
eid int primary key,
name varchar(20),
deptId int,
salary double
);
-- 2:添加自增正约束
create table t_user1 (
id int primary key auto_increment,
name varchar(20)
);
-- 3：创建非空约束
create table mydb1.t_user6 (
id int ,
name varchar(20) not null, -- 指定非空约束
address varchar(20) not null -- 指定非空约束
);
-- 4：创建唯一约束
create table t_user8 (
id int ,
name varchar(20) ,
phone_number varchar(20) unique -- 指定唯一约束
);
-- 5:创建默认约束
create table t_user10 (
id int ,
name varchar(20) ,
address varchar(20) default '北京' -- 指定默认约束
);

The above is the detailed content of How to implement MySQL's default constraint default and zero-fill constraint zerofill. For more information, please follow other related articles on the PHP Chinese website!