mysql 常用语句

mysql自动生成创建时间

ALTER TABLE base_user ADD create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间';

自动生成更新时间

ALTER TABLE base_user ADD update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间';

以上参考建表：

CREATE TABLE `base_user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `biz_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '业务id',
  `avatar` blob COMMENT '员工头像标识',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '员工姓名',
  `job_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '工号',
  `nick_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '别名、昵称',
  `mail` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '邮箱',
  `address` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '地址',
  `enable` tinyint NOT NULL DEFAULT '0' COMMENT '启用/禁用（0:启用,1:禁用）',
  `version` int NOT NULL DEFAULT '1' COMMENT '乐观锁',
  `deleted` tinyint NOT NULL DEFAULT '0' COMMENT '是否删除(0:否，1:是)',
  `create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '创建人',
  `update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '更新人',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_bizid` (`biz_id`) USING BTREE,
  UNIQUE KEY `uk_job_number` (`job_number`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;

查询表注释和字段

select column_name, column_comment from information_schema.columns where table_schema ='db' and table_name = 'tablename' ;

查看表生成的DDL

show create table table_name;

复制表结构

create table table1 like table;

复制数据

insert into table1 select * from table

批量更改数据库中某个字段的长度，生成sql的语句

SELECT CONCAT('alter table ',TABLE_NAME,' modify ',COLUMN_NAME,' varchar(100) ;') AS '生成修改语句'
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='数据库' AND COLUMN_NAME IN ('要更新的字段');

mysql一张表批量复制到另外一张表

INSERT INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM来源表 

WHERE not exists (select * from 目标表 where 目标表.比较字段 = 来源表.比较字段); 

新增表字段（快捷复制）时间字段

 ALTER TABLE test ADD create_time timestamp not null default CURRENT_TIMESTAMP COMMENT '创建时间';
 ALTER TABLE test ADD update_time timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新时间';

修改数据库root密码

-- 连接到 MySQL 服务器
mysql -u root

-- 修改 root 用户密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

-- 刷新权限
FLUSH PRIVILEGES;