mysql数据库日期以字符串形式存储

1.创建表语句

/*Navicat MySQL Data TransferSource Server         : localSource Server Version : 50527Source Host           : localhost:3306Source Database       : sshTarget Server Type    : MYSQLTarget Server Version : 50527File Encoding         : 65001Date: 2017-08-08 14:35:38*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for operation_log-- ----------------------------DROP TABLE IF EXISTS `operation_log`;CREATE TABLE `operation_log` (  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '日志id',  `operation_des` varchar(50) DEFAULT NULL COMMENT '操作描述',  `operation_time` varchar(50) DEFAULT NULL COMMENT '操作时间：日期格式为：2017-08-08 14:51:35',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8;-- ------------------------------ Records of operation_log-- ----------------------------INSERT INTO `operation_log` VALUES ('1', '凭证踪迹管理-查看', '2017-08-08 14:51:35');INSERT INTO `operation_log` VALUES ('2', null, '2017-08-08 14:51:37');INSERT INTO `operation_log` VALUES ('3', '证照信息-修改', '2017-08-08 15:00:03');INSERT INTO `operation_log` VALUES ('4', '证照信息-查看', '2017-08-08 15:00:05');INSERT INTO `operation_log` VALUES ('5', null, '2017-08-08 15:00:07');INSERT INTO `operation_log` VALUES ('6', '证照信息-修改', '2017-08-08 15:00:12');INSERT INTO `operation_log` VALUES ('7', '证照信息-查看', '2017-08-08 15:00:13');INSERT INTO `operation_log` VALUES ('8', null, '2017-08-08 15:00:16');INSERT INTO `operation_log` VALUES ('9', '证照信息-修改', '2017-08-08 15:00:17');INSERT INTO `operation_log` VALUES ('10', '证照信息-修改', '2017-08-08 15:00:18');INSERT INTO `operation_log` VALUES ('11', null, '2017-08-08 15:01:08');INSERT INTO `operation_log` VALUES ('12', '证照信息-修改', '2017-08-08 15:01:09');INSERT INTO `operation_log` VALUES ('13', '证照信息-修改', '2017-08-08 15:01:10');INSERT INTO `operation_log` VALUES ('14', '证照信息-查看', '2017-08-08 15:01:12');INSERT INTO `operation_log` VALUES ('15', null, '2017-08-08 14:52:48');INSERT INTO `operation_log` VALUES ('16', null, '2017-08-08 15:01:14');INSERT INTO `operation_log` VALUES ('17', '证照信息-修改', '2017-08-08 15:01:15');INSERT INTO `operation_log` VALUES ('18', '证照信息-修改', '2017-08-08 15:01:16');INSERT INTO `operation_log` VALUES ('19', '证照信息-查看', '2017-08-08 15:01:18');INSERT INTO `operation_log` VALUES ('20', '凭证踪迹管理-查看', '2017-08-08 14:52:54');INSERT INTO `operation_log` VALUES ('21', '凭证踪迹管理-查看', '2017-08-08 14:52:56');INSERT INTO `operation_log` VALUES ('22', null, '2017-08-08 14:52:57');INSERT INTO `operation_log` VALUES ('23', null, '2017-08-08 14:53:16');INSERT INTO `operation_log` VALUES ('24', null, '2017-08-08 14:53:19');INSERT INTO `operation_log` VALUES ('25', null, '2017-08-08 15:01:46');INSERT INTO `operation_log` VALUES ('26', '证照信息-修改', '2017-08-08 15:01:48');INSERT INTO `operation_log` VALUES ('27', null, '2017-08-08 14:53:24');INSERT INTO `operation_log` VALUES ('28', null, '2017-08-08 14:53:27');INSERT INTO `operation_log` VALUES ('29', '证照信息-修改', '2017-08-08 15:02:01');INSERT INTO `operation_log` VALUES ('30', '证照信息-查看', '2017-08-08 15:02:03');INSERT INTO `operation_log` VALUES ('31', null, '2017-08-08 15:02:37');INSERT INTO `operation_log` VALUES ('32', '凭证踪迹管理-查看', '2017-08-08 14:54:50');INSERT INTO `operation_log` VALUES ('33', null, '2017-08-08 15:03:17');INSERT INTO `operation_log` VALUES ('34', null, '2017-08-08 14:54:51');INSERT INTO `operation_log` VALUES ('35', '证照信息-修改', '2017-08-08 15:03:21');INSERT INTO `operation_log` VALUES ('36', null, '2017-08-08 14:54:57');INSERT INTO `operation_log` VALUES ('37', null, '2017-08-08 14:54:59');INSERT INTO `operation_log` VALUES ('38', '证照信息-修改', '2017-08-08 15:03:33');INSERT INTO `operation_log` VALUES ('39', '证照信息-查看', '2017-08-08 15:03:35');INSERT INTO `operation_log` VALUES ('40', '证照信息-查看', '2017-08-08 15:05:09');INSERT INTO `operation_log` VALUES ('41', null, '2017-08-08 15:05:10');INSERT INTO `operation_log` VALUES ('42', null, '2017-08-08 15:06:14');INSERT INTO `operation_log` VALUES ('43', null, '2017-08-08 15:07:08');INSERT INTO `operation_log` VALUES ('44', '证照信息-修改', '2017-08-08 15:07:09');INSERT INTO `operation_log` VALUES ('45', '证照信息-修改', '2017-08-08 15:07:11');INSERT INTO `operation_log` VALUES ('46', '证照信息-查看', '2017-08-08 15:07:13');

2.表结构截图：
表名：operation_log 日期字段：operation_time

3.相关sql语句：
（1）以日期字段operation_time排序（升序和降序查询）：

select * from operation_log order by str_to_date(operation_time, '%Y-%m-%d %H:%i:%s') asc;

select * from operation_log order by str_to_date(operation_time, '%Y-%m-%d %H:%i:%s') desc;

（2）以operation_des模糊查询：

select * from operation_log where operation_des like '%证照%';

（3）以日期字段operation_time条件查询，起始日期和结束日期：

select * from operation_log where str_to_date(operation_time, '%Y-%m-%d %H:%i:%s') >= str_to_date('2017-08-08 14:52:48', '%Y-%m-%d %H:%i:%s') and str_to_date(operation_time, '%Y-%m-%d %H:%i:%s') <= str_to_date('2017-08-08 15:52:48', '%Y-%m-%d %H:%i:%s');

在mybatis中的sql语句写法：

CONCAT('%',#{operationDes},'%')<![CDATA[ <= ]]>

<!-- 根据条件查询所有信息 -->  <select id="testSelect" resultMap="BaseResultMap" parameterType="">    select     <include refid="Base_Column_List" />    from operation_log     where 1=1      <if test=" operationDes !=null and operationDes !='' ">          and  operation_des like CONCAT('%',#{operationDes},'%')    </if>      <if test=" operationTime !=null and operationTime !='' ">          and  str_to_date(operation_time, '%Y-%m-%d %H:%i:%s') <![CDATA[ >= ]]> str_to_date(#{operationTime}, '%Y-%m-%d %H:%i:%s')    </if>     <if test=" operationEndtime !=null and operationEndtime !='' ">          and  str_to_date(operation_time, '%Y-%m-%d %H:%i:%s')<![CDATA[ <= ]]> str_to_date(#{operationEndtime}, '%Y-%m-%d %H:%i:%s')    </if>  </select>