MySQL常见操作

• 创建用户并授权

  GRANT CREATE ROUTINE, ALTER ROUTINE, SELECT, CREATE, ALTER, INSERT, UPDATE, DELETE, DROP, EXECUTE
  ON hdk_service.*
  TO 'user'@'%'
  IDENTIFIED BY 'password'
  

• 变态的Mysql列不存在时添加列写法(首先需要通过上面的SQL拿到创建存储过程的权限)

  USE hdk_service;
  
  delimiter //
  
  CREATE PROCEDURE accounts_add_column ()
  BEGIN
     IF NOT EXISTS(
          SELECT * FROM information_schema.COLUMNS
          WHERE COLUMN_NAME='code' AND TABLE_NAME='accounts' AND TABLE_SCHEMA='hdk_service'
          )
      THEN
          ALTER TABLE `hdk_service`.`accounts`
          ADD COLUMN `code` varchar(50) NULL;
  
      END IF;
      IF NOT EXISTS(
          SELECT * FROM information_schema.COLUMNS
          WHERE COLUMN_NAME='start_time' AND TABLE_NAME='accounts' AND TABLE_SCHEMA='hdk_service'
          )
      THEN
          ALTER TABLE `hdk_service`.`accounts`
          ADD COLUMN `start_time` DATETIME NULL;
  
      END IF;
  END
  //
  
  delimiter ;
  
  CALL accounts_add_column();
  
  DROP PROCEDURE accounts_add_column;
  

• Mac 下通过dmg安装完mysql之后设置 root 密码

  mysqladmin -u root password 新密码
  

  Note

  上面除了’新密码’外其他都是关键字