MySQL Points

日期格式化

DATE_FORMAT(yourtime, '%Y-%m-%d %T') OR DATE_FORMAT(yourtime, '%Y-%m-%d %H:%i:%s');

mysql 反转匹配

locate(reverse(?), reverse(b.pid)) = 1 from MerchantPromo m where locate(reverse(?), reverse(m.pid)) = 1

STRICT mode（严格模式）

select @@global.sql_mode

set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

double小数位数精度：

FORMAT(doublenum, number);

修改数据库字符集：

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

修改表的字符集：

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

修改某个表的字段字符集

ALTER TABLE table_name CHANGE column_name column_name LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

删除列

ALTER TABLE sm_member DROP COLUMN update_user;

解除外键约束

ALTER TABLE sm_member DROP FOREIGN KEY FK_q3aqiekr8joc1yq86xm743j6w;

将mysql的权限开放给数据库客户端

ALTER user 'root'@'localhost' identified with mysql_native_password by '自己的密码'; 
use mysql;
select host, user from user;
update user set host="%" where user='root';
select host, user from user;
flush privileges;
GRANT ALL ON *.* TO 'root'@'%';
flush privileges;

​

​