mysql修改表时添加默认约束和删除默认约束

mysql修改表时添加默认约束和删除默认约束

直接po代码和截图

#
CREATE TABLE testMyIndex2(
id int,
address VARCHAR(130),
email VARCHAR(40),
hobby VARCHAR(160),
userName VARCHAR(50)
);

#修改表时添加默认约束
#
ALTER TABLE testMyIndex2 MODIFY address VARCHAR(115) DEFAULT '江西省赣州市于都县';
SELECT * FROM testMyIndex2;
INSERT INTO testMyIndex2(id, email, hobby, userName) VALUES (1, 'ling@qq.com', '喝酒', '令狐冲');
INSERT INTO testMyIndex2(id, email, address, hobby, userName) VALUES (4, 'yang@qq.com', DEFAULT, '打架', '杨过');
SELECT * FROM testMyIndex2;
#
ALTER TABLE testMyIndex2 ALTER COLUMN address SET DEFAULT '中国江西省于都县';
SELECT * FROM testMyIndex2;
INSERT INTO testMyIndex2(id, email, hobby, userName) VALUES (2, 'wei@qq.com', '喜欢瞎掰', '韦小宝');
INSERT INTO testMyIndex2(id, email, address, hobby, userName) VALUES (3, 'zhang@qq.com', DEFAULT, '练武功', '张无忌');
SELECT * FROM testMyIndex2;

#删除默认约束(有2种方式)
#删除默认约束(方式1)
ALTER TABLE testMyIndex2 MODIFY address VARCHAR(90);
#
DESC testMyIndex2;
SELECT * FROM testMyIndex2;
#插入数据时，不会报错，address列为null
INSERT INTO testMyIndex2(id, email, address, hobby, userName) VALUES (5, 'guo@qq.com', DEFAULT, '看书', '郭靖');
SELECT * FROM testMyIndex2;

#删除默认约束(方式2)
ALTER TABLE testMyIndex2 ALTER COLUMN address DROP DEFAULT;
#
DESC testMyIndex2;
/*如果是通过第2种方式删除默认约束，那么如下这样插入数
据时会报错1364 - Field 'address' doesn't have a default value
*/
INSERT INTO testMyIndex2(id, email, address, hobby, userName) VALUES (6, 'huang@qq.com', DEFAULT, '聪明伶俐', '黄蓉');
SELECT * FROM testMyIndex2;

#
DESC testMyIndex2;
#正确插入数据
INSERT INTO testMyIndex2(id, email, address, hobby, userName) VALUES (7, 'jian@qq.com', '紫禁城', '嚣张跋扈', '建宁公主');

/*如果是通过第2种方式删除默认约束，那么如下这样插入数
据时会报错1364 - Field 'address' doesn't have a default value
*/
INSERT INTO testMyIndex2(id, email, hobby, userName) VALUES (8, 'shuang@qq.com', '武功', '双儿');
#
DESC testMyIndex2;
#可以查一下建表的信息，分析一下为什么上面的那条INSERT语句为什么无法插入数据，为什么报错?
SHOW CREATE TABLE testMyIndex2;
#
SELECT * FROM testMyIndex2;
#
INSERT INTO testMyIndex2(id, email, address, hobby, userName) VALUES (9, 'mu@qq.com', '沐王府', '小郡主', '沐剑屏');
#
SELECT * FROM testMyIndex2;