/*
Navicat MySQL Data Transfer

Source Server : hlm
Source Server Type : MySQL
Source Server Version : 50720
Source Host : localhost:3306
Source Schema : mytest

Target Server Type : MySQL
Target Server Version : 50720
File Encoding : 65001

Date: 21/05/2019 22:30:41
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Table structure for t_user

DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user (
user_id int(11) NOT NULL AUTO_INCREMENT,
user_name varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
password varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
phone varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (user_id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- Records of t_user

INSERT INTO t_user VALUES (2, 'job', 'ad', '1523132154');
INSERT INTO t_user VALUES (3, 'name', '1234', '370683198901117657');
INSERT INTO t_user VALUES (8, 'jok', '789', '130131199205243311');
INSERT INTO t_user VALUES (9, 'w', 'qq', 'ew');
INSERT INTO t_user VALUES (10, 'w', 'QQ', 'WA');

SET FOREIGN_KEY_CHECKS = 1;

select phone,CAST(SUBSTRING(phone,7,8) AS DATETIME) AS bir,FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(SUBSTRING(phone,7,8) AS DATETIME)),'%m-%d') AS TIME,IF(LEFT(SUBSTRING(phone,17),1)%2=1,"男","女") AS sex FROM t_user where phone REGEXP '^[1-9][[:digit:]]{7}((0[[:digit:]])|(1[0-2]))(([0|1|2][[:digit:]])|3[0-1])[[:digit:]]{3}$|^[1-9][[:digit:]]{5}[1-9][[:digit:]]{3}((0[[:digit:]])|(1[0-2]))(([0|1|2][[:digit:]])|3[0-1])[[:digit:]]{3}([0-9]|X)$'