docker 安装mysql
mysql 配置文件
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
skip-host-cache
skip-name-resolve
##########################################################
port=3306
#设置服务器的默认字符集为utf8
character-set-server=utf8
#数据目录
datadir=/var/lib/mysql
#设置服务器的时区 未配置则为系统时区
default-time-zone = '+08:00'
#启用binlog 并指定文件 文件位置在datadir下
log_bin=true
log-bin=mysql-bin
binlog_format=row
server-id=1
#是否开启慢查询日志,1开启 0关闭
slow-query-log=1
#慢查询日志的输出路径
slow_query_log_file="/var/log/slow_query.log
#通用日志的输出方式TABLE,FILE,NONE,这里设置了输出到文件
log-output=FILE
#是否开启通用日志,1开启 0关闭
general-log=1
general_log_file="/var/log/general.log"
#通用日志的输出路径
#错误日志
log-error=/var/log/mysqld.err.log
#######################################
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
[client]
#设置客户端的默认字符集
default-character-set=utf8
socket=/var/run/mysqld/mysqld.sock
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
docker run -d --name mysql --hostname="mysql" -e MYSQL_ROOT_PASSWORD=123456 -v /home/docker/mysql/conf/my.cnf:/etc/mysql/my.cnf -v /home/docker/mysql/data/:/var/lib/mysql/ -v /home/docker/mysql/log/:/var/log -p 3306:3306 --network inet --rm mysql:5.7
Jdbc连接的方式
-- jdbc:mysql://hostname:port/schema?useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=Asia/Shanghai&allowMultiQueries=true
查询表结构
-- 利用 information_schema 查询
SELECT x.TABLE_SCHEMA,x.COLUMN_NAME,x.DATA_TYPE,x.COLUMN_COMMENT,x.COLUMN_DEFAULT
FROM information_schema.COLUMNS x
WHERE table_schema = 'table_schema' AND table_name = 'table_name';
函数
COALESCE ( expression,value1,value2……)
-- 函数的第一个参数expression为待检测的表达式，而其后的参数个数不定。
-- expression 不为空返回 expression 否则 判断value1不为空返回value1
-- 否则判断value2不为空返回value2 以此类推