How to set the default value of a MySQL field

# Format template <field name> <data type> DEFAULT <default value>

# Examplemysql> CREATE TABLE `test_tb` (
    -> `id` int NOT NULL AUTO_INCREMENT,
    -> `col1` varchar(50) not null DEFAULT 'a',
    -> `col2` int not null DEFAULT 1,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> desc test_tb;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| col1 | varchar(50) | NO | | a | |
| col2 | int(11) | NO | | 1 | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into test_tb (col1) values ​​('fdg');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_tb (col2) values ​​(2);
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_tb;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
| 1 | fdg | 1 |
| 2 | a | 2 |
+----+------+------+
2 rows in set (0.00 sec)

# Add a new field and set the default value alter table `test_tb` add column `col3` varchar(20) not null DEFAULT 'abc';

# Modify the original default value alter table `test_tb` alter column `col3` set default '3a';
alter table `test_tb` change column `col3` `col3` varchar(20) not null DEFAULT '3b';
alter table `test_tb` MODIFY column `col3` varchar(20) not null DEFAULT '3c';

# Delete the original default value alter table `test_tb` alter column `col3` drop default;

# Increase default value (similar to modification)
alter table `test_tb` alter column `col3` set default '3aa';

mysql> alter table `test_tb` add column `col4` varchar(20) DEFAULT '4a';
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test_tb;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| col1 | varchar(50) | NO | | a | |
| col2 | int(11) | NO | | 1 | |
| col3 | varchar(20) | NO | | 3aa | |
| col4 | varchar(20) | YES | | 4a | |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

CREATE TABLE `default_tb` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key',
  ...
  `country` varchar(50) not null DEFAULT '中国',
  `col_status` tinyint not null DEFAULT 1 COMMENT '1: What does it represent 2: What does it represent...',
  `col_time` datetime NOT NULL DEFAULT '2020-10-01 00:00:00' COMMENT 'What time',
  `is_deleted` tinyint not null DEFAULT 0 COMMENT '0: Not deleted 1: Deleted',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time',
  PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;