mysql

常用sql

查询数据库每个表的行数

select table_name,table_rows from information_schema.tables where TABLE_SCHEMA = 'test' order by table_rows desc; 

mysql时间转换

select FROM_UNIXTIME(1156219870); //时间戳转为日期
select UNIX_TIMESTAMP('2006-11-04 12:23:00');  //日期转为时间戳
//格式化shij
SELECT FROM_UNIXTIME(last_login, '%Y-%m-%d %H:%i:%s') FROM `test`;

授予权限

grant all on ydd.* to www@'%' ;
flush privileges;

#创建用户

create user 'www'@'%' identified by 'iamsuperboy';

触发器

查看

//查询test数据库的触发器
SELECT * FROM information_schema.`TRIGGERS` WHERE TRIGGER_SCHEMA='test';
//在当前数据库的话
SHOW TRIGGERS;

创建

CREATE TRIGGER insert_order_sn BEFORE INSERT ON ecs_order_info FOR EACH ROW
BEGIN
   SET @dt = DATE_FORMAT(NOW(),'%Y%m%d');  
   SET @number = (SELECT count FROM ecs_order_counts WHERE date=@dt);
   IF @number IS NULL  THEN  
     insert into `ecs_order_counts` (`count`,`date`) VALUES(1,@dt);
     SET @number =0;
   END IF; 
   SET @number=@number+1;
   IF @number < 10 THEN
      SET @order_sn=CONCAT(@dt,'000',@number);
   ELSEIF @number>=10 and @number <100 THEN
      SET @order_sn=CONCAT(@dt,'00',@number);
   ELSEIF @number>=100 and @number <1000 THEN
      SET @order_sn=CONCAT(@dt,'0',@number);
   ELSEIF @number>=1000 THEN
      SET @order_sn=CONCAT(@dt,@number);
   END IF;
   SET NEW.order_sn=@order_sn;
   UPDATE `ecs_order_counts` set `count`=@number where date=@dt;
END

删除

DROP TRIGGER insert_order_sn

常见问题

#5.7 Invalid default value for 'start_time'

  1. 打开/etc/mysql/mysql.conf.d/mysqld.cnf
  2. 查找: sql_mode, 在 [mysqld]的下面,如果没有的话就添加一个
sql_mode=NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

3.重启mysql

sudo service mysql restart

4.登录mysql查看是否是设置的

select @@sql_mode