image
mysql 汇总
基础操作
史上最全MySQL基本操作（这一篇就够用了！！！）
表管理
查看表结构
show full columns from sys_user ;
查看表注释
selectTABLE_NAME as 表名,TABLE_COMMENT as 表注释
fromINFORMATION_SCHEMA.TABLES
whereTABLE_SCHEMA = 'shiro_study';
查看表索引
show index from sys_user ;
基础查询
vim
.vimrc
" 开启语法高亮
syntax enable" 开启语法高亮
syntax on" 设置字体
set guifont=Monaco\ 12" 检测文件类型
filetype on" 针对不同的文件，采用不同的缩进方式
filetype indent on" 设置取消备份，禁止临时文件生成
set nobackup
set noswapfile" 显示当前行号和列号
set ruler" 在状态栏显示正在输入的命令
set showcmd" 左下角显示当前Vim模式
set showmode" 显示状态栏
set laststatus=2" 显示行号
set number" 开启及时搜索(is)
set incsearch" 设置搜索高亮(hlsearch)
set hls" 设置搜索时忽略大小写
set ignorecase
日志
logback
<?xml version="1.0" encoding="UTF-8"?>
<!-- scan: 当此属性设置为true时，配置文件如果发生改变，将会被重新加载，默认值为true。 -->
<!-- scanPeriod: 设置监测配置文件是否有修改的时间间隔，如果没有给出时间单位，默认单位是毫秒。当scan为true时，此属性生效。默认的时间间隔为1分钟。 -->
<!-- debug: 当此属性设置为true时，将打印出logback内部日志信息，实时查看logback运行状态。默认值为false -->
<configuration scan="true" scanPeriod="60 seconds" debug="false"><property name="LOG_PATH" value="../logs/km-ssm" /><property name="LOG_FILE_NAME" value="app" /><property name="PATTERN_FORMAT"value="%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %-40.40logger{39} [tid=%X{tid}] - %msg%n" /><!-- 输出到控制台 --><appender name="console" class="ch.qos.logback.core.ConsoleAppender"><encoder><pattern>${PATTERN_FORMAT}</pattern><charset>UTF-8</charset></encoder></appender><!-- 输出到滚动文件 --><appender name="rolling-file" class="ch.qos.logback.core.rolling.RollingFileAppender"><file>${LOG_PATH}/${LOG_FILE_NAME}.log</file><encoder><pattern>${PATTERN_FORMAT}</pattern><charset>UTF-8</charset></encoder><rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy"><fileNamePattern>${LOG_PATH}/%d{yyyy-MM-dd}/${LOG_FILE_NAME}-%d{yyyy-MM-dd}.%i.log</fileNamePattern><timeBasedFileNamingAndTriggeringPolicyclass="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP"><maxFileSize>10MB</maxFileSize></timeBasedFileNamingAndTriggeringPolicy><!-- 日志文件保留天数 --><maxHistory>150</maxHistory></rollingPolicy></appender><root level="DEBUG"><appender-ref ref="console" /><appender-ref ref="rolling-file" /></root><logger name="com.laolang.km" level="INFO" additivity="false"><appender-ref ref="console" /><appender-ref ref="rolling-file" /></logger>
</configuration>
log4j2
<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="WARN"><properties><property name="LOG_HOME">../logs/jx-boot</property><property name="FILE_NAME">app</property><property name="jx.level">info</property></properties><Appenders><Console name="Console" target="SYSTEM_OUT"><PatternLayout pattern="%d{HH:mm:ss.SSS} [%25t] %-5level %l - %msg%n"/></Console><RollingRandomAccessFile name="RollingRandomAccessFile" fileName="${LOG_HOME}/${FILE_NAME}.log"filePattern="${LOG_HOME}/${date:yyyy-MM-dd}/${FILE_NAME}-%d{yyyy-MM-dd}-%i.log"><PatternLayout pattern="%d{HH:mm:ss.SSS} [%25t] %-5level %l - %msg%n"/><Policies><TimeBasedTriggeringPolicy interval="1"/><SizeBasedTriggeringPolicy size="10 MB"/></Policies><DefaultRolloverStrategy max="20"/></RollingRandomAccessFile><Socket name="logstash" host="localhost" port="1218" protocol="TCP"><PatternLayout pattern="${%d{HH:mm:ss.SSS} [%25t] %-5level %l - %msg%n}"/></Socket></Appenders><Loggers><Root level="${jx.level}"><AppenderRef ref="Console"/><AppenderRef ref="RollingRandomAccessFile"/><AppenderRef ref="logstash"/></Root><Logger name="com.laolang" level="${jx.level}" additivity="false"><AppenderRef ref="Console"/><AppenderRef ref="RollingRandomAccessFile"/><AppenderRef ref="logstash"/></Logger></Loggers>
</Configuration>
mybatis
打印 sql
package com.laolang.jx.framework.mybatis.interceptor;import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import java.lang.reflect.Field;
import java.lang.reflect.Proxy;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;/*** 打印 sql 拦截器*/
@Slf4j
@Intercepts({@Signature(type = StatementHandler.class, method = "query", args = {Statement.class, ResultHandler.class}),@Signature(type = StatementHandler.class, method = "update", args = {Statement.class}),@Signature(type = StatementHandler.class, method = "batch", args = {Statement.class})})
public class MybatisPrintSqlInterceptor implements Interceptor {/*** mybatis 配置对象.*/private Configuration configuration = null;/*** 时间格式化.*/private static final ThreadLocal<SimpleDateFormat> DATE_FORMAT_THREAD_LOCAL = ThreadLocal.withInitial(() -> new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"));/*** 拦截器主方法.** @param invocation invocation* @return sql 执行结果* @throws Throwable Throwable*/@SuppressWarnings("rawtypes")@Overridepublic Object intercept(Invocation invocation) throws Throwable {long startTime = System.currentTimeMillis();Object result = null;try {result = invocation.proceed();return result;} finally {try {long endTime = System.currentTimeMillis();long sqlCost = endTime - startTime;StatementHandler statementHandler = getRealTarget(invocation.getTarget());BoundSql boundSql = statementHandler.getBoundSql();if (configuration == null) {final ParameterHandler parameterHandler = statementHandler.getParameterHandler();// Field configurationField = ReflectionUtils.findField(parameterHandler.getClass(), "configuration");// ReflectionUtils.makeAccessible(configurationField);Field configurationField = ReflectUtil.getField(parameterHandler.getClass(), "configuration");ReflectUtil.setAccessible(configurationField);this.configuration = (Configuration) configurationField.get(parameterHandler);}// 输出 mapper idMetaObject metaObject = SystemMetaObject.forObject(statementHandler);MappedStatement ms = (MappedStatement) metaObject.getValue("delegate.mappedStatement");String id = ms.getId();// 替换参数格式化Sql语句，去除换行符String sql = formatSql(boundSql, configuration).concat(";");String warning = "";// CHECKSTYLE:OFFif (sqlCost > 2000) {warning = "[耗时过长]";}// CHECKSTYLE:ON// 开始输出 sqllog.info("map-id: {}", id);log.info("[ {} ] [ {} ] ms {}", sql, sqlCost, warning);if (result instanceof List) {log.info("Total: {}", ((List) result).size());} else {log.info("Updates: {}", result);}} catch (Exception e) {log.error("==> 打印sql 日志异常 {0}", e);}}}/*** <p>* 获取真正的对象（非代理对象）* </p>* <p>* 解决报错:* <code>There is no getter for property named 'delegate' in 'class com.sun.proxy.$Proxy199'</code>*/@SuppressWarnings("unchecked")public static <T> T getRealTarget(Object target) {if (Proxy.isProxyClass(target.getClass())) {MetaObject metaObject = SystemMetaObject.forObject(target);return getRealTarget(metaObject.getValue("h.target"));}return (T) target;}/*** plugin.** @param target target* @return Object*/@Overridepublic Object plugin(Object target) {return Plugin.wrap(target, this);}/*** setProperties.** @param properties properties*/@Overridepublic void setProperties(Properties properties) {}/*** 获取完整的sql实体的信息.** @param boundSql boundSql* @param configuration configuration* @return 格式化后的 sql*/private String formatSql(BoundSql boundSql, Configuration configuration) {String sql = boundSql.getSql();Object parameterObject = boundSql.getParameterObject();// 输入sql字符串空判断if (StrUtil.isBlank(sql)) {return "";}if (configuration == null) {return "";}TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();sql = beautifySql(sql);List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();// 参考mybatis 源码 DefaultParameterHandlerif (parameterMappings != null) {for (ParameterMapping parameterMapping : parameterMappings) {if (parameterMapping.getMode() != ParameterMode.OUT) {Object value;String propertyName = parameterMapping.getProperty();if (boundSql.hasAdditionalParameter(propertyName)) {value = boundSql.getAdditionalParameter(propertyName);} else if (parameterObject == null) {value = null;} else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {value = parameterObject;} else {MetaObject metaObject = configuration.newMetaObject(parameterObject);value = metaObject.getValue(propertyName);}String paramValueStr = "";if (value instanceof String) {paramValueStr = "'" + value + "'";} else if (value instanceof Date) {paramValueStr = "'" + DATE_FORMAT_THREAD_LOCAL.get().format(value) + "'";} else {paramValueStr = value + "";}sql = sql.replaceFirst("\\?", paramValueStr);}}}return sql;}/*** 美化 sql.** @param sql sql* @return sql*/private String beautifySql(String sql) {sql = sql.replaceAll("[\\s\n ]+", " ");return sql;}
}
