mysql 格式化时间日期 DATE_FORMAT,FROM_UNIXTIME,UNIX_TIME等

[mysql]

SELECT phone,FROM_UNIXTIME(ctime,’%Y-%c-%d %H:%i:%s’) 时间 from wx_yuyue WHERE ctime>1493568000 ORDER BY  ctime desc

[/mysql]

 

知识关键词:DATE_FORMAT

  1. select DATE_FORMAT(create_time,‘%Y%u’) weeks,count(caseid) count from tc_case group by weeks;
  2. select DATE_FORMAT(create_time,‘%Y%m%d’) days,count(caseid) count from tc_case group by days;
  3. select DATE_FORMAT(create_time,‘%Y%m’) months,count(caseid) count from tc_case group by months;

DATE_FORMAT(date,format)

根据format字符串格式化date值。下列修饰符可以被用在format字符串中:

%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday )
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 一个文字“%”。

 

对比DATE_FORMAT,FROM_UNIXTIME,UNIX_TIME

1. DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

Sql代码  收藏代码
  1. DATE_FORMAT(date,format)

 

可以使用的格式有:

格式 描述

%a 缩写星期名
%b 缩写月名
%c 月,数值
%D 带有英文前缀的月中的天
%d 月的天,数值(00-31)
%e 月的天,数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟,数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月,数值(00-12)
%p AM 或 PM
%r 时间,12-小时(hh:mm:ss AM 或 PM)
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天,与 %X 使用
%v 周 (01-53) 星期一是一周的第一天,与 %x 使用
%W 星期名
%w 周的天 (0=星期日, 6=星期六)
%X 年,其中的星期日是周的第一天,4 位,与 %V 使用
%x 年,其中的星期一是周的第一天,4 位,与 %v 使用
%Y 年,4 位
%y 年,2 位

 

实例

下面的脚本使用 DATE_FORMAT() 函数来显示不同的格式。我们使用 NOW() 来获得当前的日期/时间:

Sql代码  收藏代码
  1. DATE_FORMAT(NOW(),‘%b %d %Y %h:%i %p’)
  2. DATE_FORMAT(NOW(),‘%m-%d-%Y’)
  3. DATE_FORMAT(NOW(),‘%d %b %y’)
  4. DATE_FORMAT(NOW(),‘%d %b %Y %T:%f’)

 

结果类似:

  1. Dec 29 2008 11:45 PM
  2. 12-29-2008
  3. 29 Dec 08
  4. 29 Dec 2008 16:25:46

 

如果要格式表中的数据,可以用:

 

2. MySQL 数据库中日期与时间函数 FROM_UNIXTIME(), UNIX_TIME() …

 

实例: date => int(11)

  1. SELECT FROM_UNIXTIME(date‘%Y-%c-%d %h:%i:%s’ ) as post_date ,
  2. date_format(NOW(), ‘%Y-%c-%d %h:%i:%s’ ) as post_date_gmt
  3. FROM `article`  where outkey = ‘Y’

 

1、FROM_UNIXTIME( unix_timestamp )
参数:通常是壹个十位的数字,如:1344887103
返回值:有两种,可能是类似 ‘YYYY-MM-DD HH:MM:SS’ 这样的字符串,也有可能是类似于 YYYYMMDDHHMMSS.uuuuuu 这样的数字,具体返回什么取决于该函数被调用的形式。

  1. mysql> select FROM_UNIXTIME(1344887103);
  2. +—————————+
  3. | FROM_UNIXTIME(1344887103) |
  4. +—————————+
  5. | 2012-08-14 03:45:03       |
  6. +—————————+
  7. 1 row in set (0.00 sec)

 

2、FROM_UNIXTIME( unix_timestamp ,format )
参数 unix_timestamp :与方法 FROM_UNIXTIME( unix_timestamp ) 中的参数含义一样;
参数 format : 转换之后的时间字符串显示的格式;
返回值:按照指定的时间格式显示的字符串;

  1. mysql> select FROM_UNIXTIME(1344887103,‘%Y-%M-%D %h:%i:%s’);
  2. +———————————————–+
  3. | FROM_UNIXTIME(1344887103,‘%Y-%M-%D %h:%i:%s’) |
  4. +———————————————–+
  5. | 2012-August-14th 03:45:03                     |
  6. +———————————————–+
  7. 1 row in set (0.00 sec)
  8. mysql> select FROM_UNIXTIME(1344887103,‘%Y-%m-%D %h:%i:%s’);
  9. +———————————————–+
  10. | FROM_UNIXTIME(1344887103,‘%Y-%m-%D %h:%i:%s’) |
  11. +———————————————–+
  12. | 2012-08-14th 03:45:03                         |
  13. +———————————————–+
  14. 1 row in set (0.00 sec)

 

 

 

1、UNIX_TIMESTAMP()

返回值:当前时间的UNIX格式数字串,或者说是 UNIX 时间戳(从 UTC 时间’1970-01-01 00:00:00’开始的秒数),通常为十位,如 1344887103。

Sql代码  收藏代码
  1. mysql> select unix_timestamp();
  2. +——————+
  3. | unix_timestamp() |
  4. +——————+
  5. |       1344887103 |
  6. +——————+
  7. 1 row in set (0.00 sec)

 

2、UNIX_TIMESTAMP( date )
参数:date 可能是个 DATE 字符串,DATETIME 字符串,TIMESTAPE 字符串,或者是一个类似于 YYMMDD 或者 YYYYMMDD 的数字串。
返回:从 UTC 时间’1970-01-01 00:00:00’开始到该参数之间的秒数。服务器将参数 date 解释成当前时区的壹个值并且将其转化成 UTC 格式的内部时间。客户端则可以自行设置当前时区。当 UNIX_TIMESTAMP() 用于壹个 TIMESTAMP 列时,函数直接返回内部时间戳的值;如果你传递壹个超出范围的时间到 UNIX_TIMESTAMP(),它的返回值是零。

  1. mysql> SELECT UNIX_TIMESTAMP();
  2. +——————+
  3. | UNIX_TIMESTAMP() |
  4. +——————+
  5. |       1344888895 |
  6. +——————+
  7. 1 row in set (0.00 sec)
  8. mysql> SELECT UNIX_TIMESTAMP(‘2012-08-14 16:19:23’);
  9. +—————————————+
  10. | UNIX_TIMESTAMP(‘2012-08-14 16:19:23’) |
  11. +—————————————+
  12. |                            1344932363 |
  13. +—————————————+
  14. 1 row in set (0.00 sec)

 

注意:如果你使用 UNIX_TIMESTAMP() 和 FROM_UNIXTIME() 来转换 TIMESTAMP 值与 Unix 时间戳的值,精度会丢失,因为这个映射在两个方向上不是一一对应的。比如说,由于本地时区的更改,有可能两个 UNIX_TIMESTAMP() 会映射到同壹个 Unix 时间戳的值。 FROM_UNIXTIME() 只会映射到原来的那个时间戳的值上。这里有个例子,在 CET 时区使用 TIMESTAMP:

  1. mysql> SELECT UNIX_TIMESTAMP(‘2005-03-27 03:00:00’);
  2. +—————————————+
  3. | UNIX_TIMESTAMP(‘2005-03-27 03:00:00’) |
  4. +—————————————+
  5. |                            1111885200 |
  6. +—————————————+
  7. mysql> SELECT UNIX_TIMESTAMP(‘2005-03-27 02:00:00’);
  8. +—————————————+
  9. | UNIX_TIMESTAMP(‘2005-03-27 02:00:00’) |
  10. +—————————————+
  11. |                            1111885200 |
  12. +—————————————+
  13. mysql> SELECT FROM_UNIXTIME(1111885200);
  14. +—————————+
  15. | FROM_UNIXTIME(1111885200) |
  16. +—————————+
  17. | 2005-03-27 03:00:00       |
  18. +—————————+

数据库无法启动解决办法 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace fangwei/yjcode_moneyrecord uses space

2017-03-29 10:12:37 1436 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace fangwei/yjcode_moneyrecord uses space ID: 38 at filepath: .\fangwei\yjcode_moneyrecord.ibd. Cannot open tablespace nbw1/t_cardcon514log which uses space ID: 38 at filepath: .\nbw1\t_cardcon514log.ibd
InnoDB: Error: could not open single-table tablespace file .\nbw1\t_cardcon514log.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.

 

在D:\wamp\mysql\bin下面的my.ini中添加

innodb_force_recovery = 1

thinkphp 实现多数据库 连接 解决办法

一、全局配置定义

return array(
    'DB_TYPE' => 'mysql',
    'DB_HOST' => '127.0.0.1',
    'DB_NAME' => 'thinkcmf',
    'DB_USER' => 'root',
    'DB_PWD' => 'root',
    'DB_PORT' => '3306',
    'DB_PREFIX' => 'cmf_',
    //密钥
    "AUTHCODE" => '2oF6v4m67BdQnfwMSf',
    //cookies
    "COOKIE_PREFIX" => '03a9uH_',
);

如果我们已经在配置文件中配置了额外的数据库连接信息,例如:

return array(
    //数据库1
    'DB_TYPE' => 'mysql',
    'DB_HOST' => '127.0.0.1',
    'DB_NAME' => 'thinkcmf',
    'DB_USER' => 'root',
    'DB_PWD' => 'root',
    'DB_PORT' => '3306',
    'DB_PREFIX' => 'cmf_',
    //密钥
    "AUTHCODE" => '2oF6v4m67BdQnfwMSf',
    //cookies
    "COOKIE_PREFIX" => '03a9uH_',
    //数据库2
    'DB_CONFIG2' => array(
        'db_type'  => 'mysql',
        'db_user'  => 'root',
        'db_pwd'   => 'root',
        'db_host'  => '127.0.0.1',
        'db_port'  => '3306',
        'db_name'  => 'repast'
       ),
    );

二、实例化定义

$User = M('User','cmf_','DB_CONFIG2');

windows无法启动MySQL服务 错误1067 错误InnoDB:Attemptedtoopenapreviouslyopenedtablespace.

2017-03-04 11:15:30 5716 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace nbw1/t_cardcontent510 uses space ID: 6 at filepath: .\nbw1\t_cardcontent510.ibd. Cannot open tablespace tpdb/tkink_user which uses space ID: 6 at filepath: .\tpdb\tkink_user.ibd
InnoDB: Error: could not open single-table tablespace file .\tpdb\tkink_user.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.

 

出现这个问题好几次了,每次的解决问题都是重新弄下Mysql.

 

在D:\lamp\mysql\bin下面的my.ini中添加

innodb_force_recovery = 1

OK,谢谢。

MySQL 如何让一个字段的值不能被减为负数

当用数据库存储一个数值,并且这个数值需要被增减的时候,比如物品的数量。

在这种场景下,物品的数量是不能为负数的。那如何确保在扣减这个数量的时候,不会被扣成负数呢。

假设表数据如下:

sku num
a 10
b 20

假设现在 a 的 num 要 减少10个。

用程序在操作之前判断? num 是否足够扣减 ? 这明显是不靠谱的。因为有并发的情况存在。

在并发的情况下,a 的 num 会变成 -10 当然,前提你的 sql 是这么写的:

UPDATE table SET num=num-10 WHERE sku="a"

绝对不能用程序去计算 num 的值,在赋值到 sql 中,在并发情况下  num 会变成 0,错误示例:

$data = 'select * from table where sku="a"';
$num = $data['num'] - 10;
UPDATE table SET num=$num WHERE sku="a"

所有的方案必须有正确的操作 sql 也就是,第一种 sql。

方案1:

利用数据库的事务,

在 update  的 sql 执行完成后,select num 的值判断是否小于0,如果小于,回滚。

方案2:

更改 num 字段的属性,无符号整形。

即字段属性中的  UN,Unisigned

参考 sql :

ALTER TABLE `table`
CHANGE COLUMN `num` `num` INT(11) UNSIGNED NOT NULL DEFAULT '0' ;

这种情况下,当 num 被减成负数的时候,数据库会返回错误。

Error Code: 1690. BIGINT UNSIGNED value is out of range in ......

php 数据库操作类 php,mysql,数据库,操作类


<?php
define("tabfix","ecs_");
class connection
{
private $server="127.0.0.1";
private $name="root";
private $pwd='root';
private $db="jcmjweb";
//当实例化时,打开数据连接
function __construct()
{
//1.连接数据库服务器
$con=mysql_connect($this->server,$this->name,$this->pwd) or die("服务器连接失败");

//2.选择一个数据库使用
mysql_select_db($this->db) or die("使用数据库失败");

}
//查询方法 返回结果集
function Query(&$str)
{
mysql_query("set names utf8");
mysql_query($str);
return mysql_insert_id();
}

//插入数据
function __call($name,$array)
{
die("你调用了一个不存在的方法");
}
function QueryArray(&$str)
{
mysql_query("set names utf8");
$result=mysql_query($str);
while($row=mysql_fetch_array($result))
{
$queryarray[]=$row;
}
return $queryarray;
}

function QueryCount(&$str)
{
mysql_query("set names utf8");
$result=mysql_query($str);
return mysql_num_rows($result);

}









}


?>

MySQL- ‘load data infile’语句

MySQL导入数据load data infile用法

基本语法:
load data [low_priority] [local] infile ‘file_name txt’ [replace | ignore]
into table tbl_name
[fields
[terminated by’t’]
[OPTIONALLY] enclosed by ”]
[escaped by’\’ ]]
[lines terminated by’n’]
[ignore number lines]
[(col_name, )]

load data infile语句从一个文本文件中以很高的速度读入一个表中。使用这个命令之前,mysqld进程(服务)必须已经在运行。为了安全原因,当读取位于服务器上的文本文件时,文件必须处于数据库目录或可被所有人读取。另外,为了对服务器上文件使用load data infile,在服务器主机上你必须有file的权限。

1 如果你指定关键词low_priority,那么MySQL将会等到没有其他人读这个表的时候,才把插入数据。可以使用如下的命令:
load data low_priority infile “/home/mark/data sql” into table Orders;
2 如果指定local关键词,则表明从客户主机读文件。如果local没指定,文件必须位于服务器上。
3 replace和ignore关键词控制对现有的唯一键记录的重复的处理。如果你指定replace,新行将代替有相同的唯一键值的现有行。如果你指定ignore,跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项,当找到重复键时,出现一个错误,并且文本文件的余下部分被忽略。例如:
load data low_priority infile “/home/mark/data sql” replace into table Orders;
4 分隔符
(1) fields关键字指定了文件记段的分割格式,如果用到这个关键字,MySQL剖析器希望看到至少有下面的一个选项:
terminated by分隔符:意思是以什么字符作为分隔符
enclosed by字段括起字符
escaped by转义字符
terminated by描述字段的分隔符,默认情况下是tab字符(\t)
enclosed by描述的是字段的括起字符。
escaped by描述的转义字符。默认的是反斜杠(backslash:\ )
例如:load data infile “/home/mark/Orders txt” replace into table Orders fields terminated by’,’ enclosed by ‘”‘;
(2)lines 关键字指定了每条记录的分隔符默认为’\n’即为换行符
如果两个字段都指定了那fields必须在lines之前。如果不指定fields关键字缺省值与如果你这样写的相同: fields terminated by’\t’ enclosed by ’ ” ‘ escaped by’\\’
如果你不指定一个lines子句,缺省值与如果你这样写的相同: lines terminated by’\n’
例如:load data infile “/jiaoben/load.txt” replace into table test fields terminated by ‘,’ lines terminated by ‘/n’;
5 load data infile 可以按指定的列把文件导入到数据库中。 当我们要把数据的一部分内容导入的时候,,需要加入一些栏目(列/字段/field)到MySQL数据库中,以适应一些额外的需要。比方说,我们要从Access数据库升级到MySQL数据库的时候
下面的例子显示了如何向指定的栏目(field)中导入数据:
load data infile “/home/Order txt” into table Orders(Order_Number, Order_Date, Customer_ID);
6 当在服务器主机上寻找文件时,服务器使用下列规则:
(1)如果给出一个绝对路径名,服务器使用该路径名。
(2)如果给出一个有一个或多个前置部件的相对路径名,服务器相对服务器的数据目录搜索文件。
(3)如果给出一个没有前置部件的一个文件名,服务器在当前数据库的数据库目录寻找文件。
例如: /myfile txt”给出的文件是从服务器的数据目录读取,而作为“myfile txt”给出的一个文件是从当前数据库的数据库目录下读取。
注意:字段中的空值用\N表示

MySQL字母大小写转换函数UPPER(s)、UCASE(s)、LOWER(s)和LCASE(s)

UPPER(s)函数和UCASE(s)函数

UPPER(s)或UCASE(s)函数可以将字符串s中的字母字符全部转换成大写字母。

实例:

使用UPPER函数或者UCASE函数将字符串中的所有字母字符转换为大写。SQL语句如下:

mysql>SELECT UPPER('www.baike369.com'),UCASE('Www.BaiKE369.Com');

执行结果如下:

MySQL UPPER(s)或UCASE(s)函数的执行结果

从上图中的操作结果可以看出,这两个函数分别把字符串“www.baike369.com”和“Www.BaiKE369.Com”都转换为大写字母“WWW.BAIKE369.COM”了。


LOWER(s)函数和LCASE(s)函数

LOWER(s)或者LCASE(s)函数可以将字符串s中的字母字符全部转换成小写字母。

实例:

使用LOWER函数或者LCASE函数将字符串中所有字母字符转换为小写。SQL语句如下:

mysql>SELECT LOWER('WWW.BAIKE369.COM'),LCASE('BaiKe369.Com');

执行结果如下:

MySQL LOWER(s)或者LCASE(s)函数的执行结果

从上图中的执行结果可以看出,这两个函数分别把大写字母字符都转换为小写字