mysql

[uniquejava]

Mac 安装 MySQL 8 并初始化 Awesome App 数据

下载 mysql-8.0.19-macos10.15-x86_64.dmg, 安装并设置 root 密码

在 mac 的 system preferences 中找到 mysql 的图标点 configuration， 可以看到 mysql 的安装目录为： /usr/local/mysql

$ cd /usr/local/mysql/bin
$ ./mysql --version
$ ./mysql -uroot -p

mysql > CREATE SCHEMA `awesome_app` DEFAULT CHARACTER SET utf8mb4 ;
mysql > show databases;
mysql > use awesome_app;
mysql > source /Users/xxx/xxx/src/main/resources/sql/schema.sql
mysql > source /Users/xxx/xxx/src/main/resources/sql/data.sql
mysql > select * from t_user;

PS:

1. 一般退出终端用 Ctrl + D 或 Ctrl + C
2. linux/mac 中执行当前目录下的可执行文件， 前面要加 ./， 执行 PATH 中的命令行程序不加 ./

使用Docker官方镜像安装mysql服务 (backup)

1 拉取mysql镜像，采用网易加速地址 docker pull hub.c.163.com/library/mysql:5.7

2 重命名镜像名 docker tag hub.c.163.com/library/mysql:5.7 mysql:5.7

3 创建用于挂载的目录 sudo mkdir /Users/cyper/bin/docker/mysql5.7/datadir #用于挂载mysql数据文件 sudo mkdir /Users/cyper/bin/docker/mysql5.7/conf.d #用于挂载mysql配置文件

sudo chown cyper:docker /my #修改/my目录拥有者

4 使用镜像创建容器 docker run --name mysql5.7 -p 3306:3306 -v /Users/cyper/bin/docker/mysql5.7/datadir:/var/lib/mysql -v /Users/cyper/bin/docker/mysql5.7/conf.d:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=000000 -d mysql:5.7

docker run --name mysql57 -p 3306:3306 -v /Volumes/Seagate/Docker_DataDir/mysql57/datadir:/var/lib/mysql -v /Volumes/Seagate/Docker_DataDir/mysql57/conf.d:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=000000 -d mysql:5.7

我已经将docker disk image location移到SSD中了。 不需要挂载volume.

5. 允许远程访问 docker exec -it mysql57 bash

命令解析： --name：容器名

--p：映射宿主主机端口

-v：挂载宿主目录到容器目录

-e：设置环境变量，此处指定root密码

-d：后台运行容器

6. 测试是否成功

mysql -h192.168.2.102 -p3306 -uroot -p

参考链接 https://itbilu.com/linux/docker/EyP7QP86M.html

JDBC URL

jdbc:mysql://xx.xx.xx.xx:3306/sales_anchor?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false&autoReconnect=false

[uniquejava]

远程登录报错Host is not allowed to connect to this MySQL server,

默认MySQL不允许远程登录，解决方法如下：

在装有MySQL的机器上登录MySQL mysql -u root -p密码

执行use mysql; 执行update user set host = '%' where user = 'root';这一句执行完可能会报错，不用管它。 执行FLUSH PRIVILEGES;

使用homebrew安装/卸载 MySQL5.7

brew doctor
brew update
brew upgrade # optional
brew info mysql@5.7
brew install mysql@5.7

参见： https://gist.github.com/operatino/392614486ce4421063b9dece4dfe6c21

装完以后可以修改ROOT密码， 如下：

/usr/local/opt/mysql@5.7/bin/mysql.server start
/usr/local/opt/mysql@5.7/bin/mysql_secure_installation

详见homebrew安装完后的提示：

We've installed your MySQL database without a root password. To secure it run: mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run: mysql -uroot

mysql@5.7 is keg-only, which means it was not symlinked into /usr/local, because this is an alternate version of another formula.

If you need to have mysql@5.7 first in your PATH run: echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.zshrc

For compilers to find mysql@5.7 you may need to set: export LDFLAGS="-L/usr/local/opt/mysql@5.7/lib" export CPPFLAGS="-I/usr/local/opt/mysql@5.7/include"

For pkg-config to find mysql@5.7 you may need to set: export PKG_CONFIG_PATH="/usr/local/opt/mysql@5.7/lib/pkgconfig"

To have launchd start mysql@5.7 now and restart at login: brew services start mysql@5.7 Or, if you don't want/need a background service you can just run: /usr/local/opt/mysql@5.7/bin/mysql.server start

使用brew的一个技巧

➜ ~ brew --cache /Users/cyper/Library/Caches/Homebrew

然后把预先下载的tar.gz包扔到这个目录下

再次执行brew install xxx就能跳过下载文件的步骤。

可能会碰到的问题

I had to run mysqld --initialize in order to eliminate the ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) issue. Make sure your /usr/local/var/mysqldirectory is empty first though.

[Sunmx]

--->下面命令没有指定端口，用尽各种办法没法远程访问 docker run --name mysql -e MYSQL_ROOT_PASSWORD=xxxxxxxx -d mysql:8.0.19 --->下面命令，指定了-p，执行之后，直接可以远程访问  docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=xxxxxxxx -d mysql:8.0.19

[uniquejava]

Integrate with spring boot.

spring:
  application:
    name: demo

  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/demo?characterEncoding=utf8&allowPublicKeyRetrieval=true&useSSL=false&autoReconnect=false
    username: root
    password: xxxxxxxx
    hikari:
      maximum-pool-size: 10
      minimum-idle: 5

  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    default-property-inclusion: non_null

mybatis:
  mapper-locations: classpath:mapper/*.xml
  configuration:
    map-underscore-to-camel-case: true
pagehelper:
  auto-dialect: true
  reasonable: true
  support-methods-arguments: true
  params: countSql

[uniquejava]

日期计算

见： https://stackoverflow.com/questions/3887509/mysqls-now-1-day

You can use:

NOW() + INTERVAL 1 DAY

If you are only interested in the date, not the date and time then you can use CURDATE instead of NOW:

CURDATE() + INTERVAL 1 DAY

类型转换

CAST(value AS datatype)

select cast(now() as date) from dual;

日起计算 cyper 实战

-- 7 天后
select curdate() +  interval 7 day from dual;
==> 2020-05-14

-- 7 小时前
select date_add(now(), interval -7 hour ) from dual;
==>2020-05-07 13:24:38

Time类型

格式为HH:mm:ss或HHmmss mybatis中不能映射为java的Date类型， 因为java的Date类型需要包含ymd, 解决办法是直接映射为java的String。

给MySQL date, time, timestamp 设置默认值

MySQL 8.0.13才开始支持给date 和 time 设置默认值, 语法如下：

create tabele INVOICE(
    INVOICE_DATE date DEFAULT (CURRENT_DATE)，
    INVOICE_TIME time DEFAULT (CURRENT_TIME)，
    INVOICE_DATETIME datetime DEFAULT NOW()
)

见： https://stackoverflow.com/questions/20461030/current-date-curdate-not-working-as-default-date-value

[uniquejava]

主键

取最后一次插入的值： select last_insert_id(),

insert into sys_task_reminder
select last_insert_id(),t.recurring_type_id,.. from xxx

设置auto_increment的起始值

CREATE TABLE users(...) AUTO_INCREMENT=1000

或者

ALTER TABLE users AUTO_INCREMENT=1001;