数据库相关杂记

2019独角兽企业重金招聘Python工程师标准>>>

1、MySql str_to_date函数与%Y-%m-%d%H:%i 问题简记

    

STR_TO_DATE(concat(c.departure_date, c.departure_time), '%Y-%m-%d%H:%i') < ?)
### Cause: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect datetime value: '2017-05-0818:55:00'
; SQL []; Data truncation: Truncated incorrect datetime value: '2017-05-0818:55:00'; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect datetime value: '2017-05-0818:55:00'

如上异常摘要

软件信息：

mysql版本-select version();

5.6.23-72.1-log
使用mapper版本号如下

<dependency>
   <groupId>tk.mybatis</groupId>
   <artifactId>mapper</artifactId>
   <version>3.3.0</version>
</dependency>


问题分析：单独在客户端执行STR_TO_DATE函数 没问题，组合成sql 使用sqltemplate执行也没有问题

那么问题应该就是发生在mapper上了。3.3.0版本的mapper，会因为时间少个秒的单位而报错

即：%Y-%m-%d%H:%i 标识的是年-月-日时分，而实际concat后的结果为2017-05-0818:55:00

因为这个简单的不匹配而报错，很多时候实在是让人抓狂，修改%Y-%m-%d%H:%i为%Y-%m-%d%H:%i%s 即可简单解决问题

 

2、mysql DB分区

    ALTER TABLE XXX PARTITION BY HASH (id) PARTITIONS 100;

 

3、springMVC集成mysql

    

MapperScannerConfigurer和sqlSessionFatoryBean
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
    xmlns:jms="http://www.springframework.org/schema/jms"
    xsi:schemaLocation="
    http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
    http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd 
    http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsd 
    http://www.springframework.org/schema/jms http://www.springframework.org/schema/jms/spring-jms-3.1.xsd
    http://www.springframework.org/schema/tx  http://www.springframework.org/schema/tx/spring-tx-3.1.xsd ">


    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="readWriteDbRoute" />
        <property name="mapperLocations" value="classpath*:mybatis/*Mapper.xml" />
        <property name="configLocation" value="classpath:mybatis-config.xml" />
        <property name="plugins">
            <array>
                <bean class="com.github.pagehelper.PageHelper">
                    <property name="properties">
                        <value>
                            dialect=mysql
                        </value>
                    </property>
                </bean>
            </array>
        </property>
    </bean>

    <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate" scope="prototype">
        <constructor-arg index="0" ref="sqlSessionFactory" />
    </bean>

    <!-- bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.ysma.aop.mapper" />
    </bean-->


    <bean id="readWriteDbRoute" class="com.ysma.aop.base.db.ReadWriteDbRoute">
        <property name="writeDb" ref="dataSource_aop_write" />
        <property name="readDbs">
            <list>
                <ref bean="dataSource_aop_read"/>
            </list>
        </property>
    </bean>

    <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="readWriteDbRoute"/>
    </bean>

    <tx:annotation-driven transaction-manager="txManager"/>


    <bean class="tk.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.ysma.aop.mapper"/>
    </bean>

</beans>

 

4、mysql 如何查看索引碎片

    

select table_name,engine,table_rows,data_length+index_length length,DATA_FREE from information_schema.tables where table_name='order_rel_policy';
 

optimize table order_rel_policy

 

5、数据库vo编程技巧

    

1、使用mybatis插入table时，如果主键是自增的那么在insert(obj) 时，插入成功后自增的主键是可以回填到obj中的。不过需要注意的一点是，obj的bean中需要显示定义id为自增类型如下：

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
 

ali fastJson格式化时间属性

import com.alibaba.fastjson.annotation.JSONField;
@JSONField (format="yyyy-MM-dd HH:mm:ss")

 

转载于:https://my.oschina.net/ysma1987/blog/1793812