为什么在MS SQL里select convert(varchar,OrderID)+convert(varchar,orderdate,120) from dbo.Orders
这样可以得到 123452009-06-20 12:00:00 这样的字符而在MYSQL里变成了 2010 ??我想要ID+datetime,转成string(从而成唯一值)还有,怎样才能达到下面的效果? 没办法,只对MSSQL熟悉,不熟悉MYSQLdeclare sd datetime;
set sd = select max(ADatetime) from tableA;
这样可以得到 123452009-06-20 12:00:00 这样的字符而在MYSQL里变成了 2010 ??我想要ID+datetime,转成string(从而成唯一值)还有,怎样才能达到下面的效果? 没办法,只对MSSQL熟悉,不熟悉MYSQLdeclare sd datetime;
set sd = select max(ADatetime) from tableA;
解决方案 »
- 标准SQL语句 如何取出一天时间内的数据 时间是Timestamp??
- mysql default 值错误
- 工作中遇到一个问题,请教下,大家来帮帮忙,主要是mysql中的日期问题
- 如何解决Warning: mysql_connect() [function.mysql-connect]: Can't connect to MySQL server on 'localhost' (10048)
- C API使用MySQL数据库的问题?(高分,求助)
- Redhat7.2下,编译安装mysql-3.23.51失败,请教高手!
- 判断表是否存在,存在就重命名的sql语句怎么写
- 数据库死锁
- 请问如何使用VBA直接操作远程Linux主机上的Mysql数据库?
- mysql排序问题, 如何排序
- 怎么删除MySQL数据库中重复的数据?
- Mysql指定中SHOW 的用法請教
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| OrderID | int(11) | YES | | NULL | |
| orderdate | date | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+
2 rows in set (0.06 sec)mysql>
mysql> select * from tx;
+---------+------------+
| OrderID | orderdate |
+---------+------------+
| 12345 | 2009-06-20 |
+---------+------------+
1 row in set (0.00 sec)mysql>
mysql> select OrderID,orderdate,
-> concat(OrderID,date_format(orderdate,'%Y-%m-%d %H:%i:%s')) as skey
-> from tx;
+---------+------------+--------------------------+
| OrderID | orderdate | skey |
+---------+------------+--------------------------+
| 12345 | 2009-06-20 | 123452009-06-20 00:00:00 |
+---------+------------+--------------------------+
1 row in set (0.00 sec)mysql>
Query OK, 1 row affected (0.00 sec)mysql> select @sd;
+------------+
| @sd |
+------------+
| 2009-06-20 |
+------------+
1 row in set (0.00 sec)mysql>
ERROR 1267 (HY000): Illegal mix of collations (gb2312_chinese_ci,IMPLICIT) and (
latin1_swedish_ci,COERCIBLE) for operation 'concat'
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| OrderID | varchar(10) | YES | | NULL | |
| orderdate | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)mysql> select * from tx;
+---------+------------+
| OrderID | orderdate |
+---------+------------+
| 12345 | 2009-06-20 |
+---------+------------+
1 row in set (0.00 sec)mysql> select OrderID,orderdate,
-> concat(OrderID,date_format(orderdate,'%Y-%m-%d %H:%i:%s')) as skey
-> from tx;
+---------+------------+--------------------------+
| OrderID | orderdate | skey |
+---------+------------+--------------------------+
| 12345 | 2009-06-20 | 123452009-06-20 00:00:00 |
+---------+------------+--------------------------+
1 row in set (0.00 sec)mysql>
建议你能给出你建表create table 语句,以有测试用数据,然后你自己的SQL语句,否则很难再现你的错误,也就无从判断问题。show create table yourtable; 查看你的建表语句。
no varchar(32) not null,
datetimeA datetime not null,
machineid int(11) not null)
ENGINE=InnoDB AUTO_INCREMENT=138 DEFAULT CHARSET=gb2312;
select no,datetimeA,
concat(no,CONVERT(date_format(datetimeA,'%Y-%m-%d %H:%i:%s') USING gb2312 )) as skey
from record_tmp;我用的你create table 建表测试没有任何问题
mysql> create table record_tmp(
-> no varchar(32) not null,
-> datetimeA datetime not null,
-> machineid int(11) not null)
-> ENGINE=InnoDB AUTO_INCREMENT=138 DEFAULT CHARSET=gb2312;
Query OK, 0 rows affected (0.39 sec)mysql> insert into record_tmp values ('12345','2009-06-20 14:34:45',1);
Query OK, 1 row affected (0.06 sec)mysql> select * from record_tmp;
+-------+---------------------+-----------+
| no | datetimeA | machineid |
+-------+---------------------+-----------+
| 12345 | 2009-06-20 14:34:45 | 1 |
+-------+---------------------+-----------+
1 row in set (0.06 sec)mysql> select no,datetimeA,
-> concat(no,date_format(datetimeA,'%Y-%m-%d %H:%i:%s')) as skey
-> from record_tmp;
+-------+---------------------+--------------------------+
| no | datetimeA | skey |
+-------+---------------------+--------------------------+
| 12345 | 2009-06-20 14:34:45 | 123452009-06-20 14:34:45 |
+-------+---------------------+--------------------------+
1 row in set (0.00 sec)mysql>
insert into tableA
select no,datetimeA from tableB
where concat(no,date_format(datetimeA,'%Y-%m-%d %H:%i:%s')) not in
(select concat(no,date_format(datetimeA,'%Y-%m-%d %H:%i:%s')) from tableA);
不过效率上来说,不如下面的语句。
insert into tableA
select no,datetimeA from tableB
where not exists (
select 1 from tableA
where no=tableB.no
and datetimeA=tableB.datetimeA);mysql> create table tableA(
-> no varchar(32) not null,
-> datetimeA datetime not null
-> );
Query OK, 0 rows affected (0.09 sec)mysql>
mysql> create table tableB(
-> no varchar(32) not null,
-> datetimeA datetime not null
-> );
Query OK, 0 rows affected (0.08 sec)mysql> insert into tableA
-> select no,datetimeA from tableB
-> where concat(no,date_format(datetimeA,'%Y-%m-%d %H:%i:%s')) not in
-> (select concat(no,date_format(datetimeA,'%Y-%m-%d %H:%i:%s')) from tableA);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql>
+----------+----------+
| 'a'<>'b' | 'a'!='b' |
+----------+----------+
| 1 | 1 |
+----------+----------+
1 row in set (0.00 sec)mysql> select 'a'<>'a','a'!='a';
+----------+----------+
| 'a'<>'a' | 'a'!='a' |
+----------+----------+
| 0 | 0 |
+----------+----------+
1 row in set (0.00 sec)mysql>
但是奇怪的是, 用了!=得不到正确的数据,也就是说还是1,但是用了<>后就成了12345