mysql问题记录

mac下mysql插入中文报错：

解决方案：创建数据库和数据表的时候，指定编码为utf8

CREATE DATABASE xxx DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

create table test (id INT auto_increment primary key, name varchar(20) not null) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

create user 'sopr'@'%' identified by 'sopr';

GRANT ALL PRIVILEGES ON *.* TO 'sopr'@'%' IDENTIFIED BY 'sopr' WITH GRANT OPTION;  

flush privileges;

重启一下：sudo service mysqld restart

MySQL乱码问题：

set names utf8;--这一句一定要加，尤其是拷贝代码粘贴到mysql终端上去的时候，重置一下当前的编码；
CREATE SCHEMA IF NOT EXISTS `jdsopr` DEFAULT CHARACTER SET utf8 COLLATE 'utf8_general_ci';
USE `jdsopr` ;

-- -----------------------------------------------------
-- Table `jdsopr`.`sopr_group`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `jdsopr`.`sopr_group` (
  `groupid` INT NOT NULL AUTO_INCREMENT,
  `groupname` VARCHAR(64) NOT NULL,
  `approver` VARCHAR(64) NULL,
  `createtime` DATETIME NOT NULL,
  `isdelete` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`groupid`),
  UNIQUE INDEX `groupName_UNIQUE` (`groupname` ASC))
ENGINE = InnoDB DEFAULT CHARSET=utf8;