Mysql按指定顺序排序的两种写法

SELECT `ID` FROM wp_posts WHERE `ID` in (1,2,3,4,5) ORDER BY FIELD(`ID`,5,4,3,1,2); // 纯数字字段不用处理引号,比较好拼接
SELECT `ID` FROM wp_posts WHERE `ID` in (1,2,3,4,5) ORDER BY FIND_IN_SET(`post_title`,'A,B,C,D,E'); // 一个引号全包住,搞定字符值字段
 
-- 注意:第一个参数不能是字符串,否则不起作用
-- 性能差异
SELECT id FROM cbd_hots WHERE id in (155,154) ORDER BY FIELD(`id`,154,155); // 纯数字字段不用处理引号,比较好拼接

亲测有效!

针对tp5中的这样的写法[orderRaw 代替order  ] 请使用orderRaw方法替代order

有业务需求如下:

select * from table where id IN (3,6,9,1,2,5,8,7) order by field(id,3,6,9,1,2,5,8,7);
但是也可以写成这样

->order("field(id,3,6,9,1,2,5,8,7)")
但是官方文档说了,当你的order排序中使用了SQL函数的时候,请使用orderRaw方法替代order 。

实践有效:

return Db::connect(config('database.sqla'))->name("hots")->where("id in (".$hots_ids.")")->orderRaw("field(id,".$hots_ids.")")->select();

 

 

SQL用了Union后的排序问题

有小伙伴反映:

最近使用SQL语句进行UNION查询,惊奇的发现:SQL没问题,UNION查询也没问题,都可以得到想要的结果,可是在对结果进行排序的时候,却出问题了。

 

SELECT a.id,a.username,a.mobile,a.time,a.leader,a.time
FROM (SELECT `id`,`username`,`mobile`,`time`,id AS leader 
	FROM `grouporder_leader` WHERE `courseid` = 21 AND `merchid` = 23 AND `status` = 1 
	UNION ALL 
	SELECT leadorderid,username,mobile,time,null 
	FROM `grouporder_partner` WHERE courseid=21 and status=1 and merchid=23
) AS a 
ORDER BY a.time DESC

注意事项:

1、必须声明临时表;

2、必须使用别名

mysql-blog类型

BLOB (binary large object),二进制大对象,是一个可以存储二进制文件的容器。在计算机中,BLOB常常是数据库中用来存储二进制文件的字段类型。BLOB是一个大文件,典型的BLOB是一张图片或一个声音文件,由于它们的尺寸,必须使用特殊的方式来处理(例如:上传、下载或者存放到一个数据库)。根据Eric
Raymond的说法,处理BLOB的主要思想就是让文件处理器(如数据库管理器)不去理会文件是什么,而是关心如何去处理它。但也有专家强调,这种处理大数据对象的方法是把双刃剑,它有可能引发一些问题,如存储的二进制文件过大,会使数据库的性能下降。在数据库中存放体积较大的多媒体对象就是应用程序处理BLOB的典型例子。

mysql BLOB类型

MySQL中,BLOB是个类型系列,包括:TinyBlob、Blob、MediumBlob、LongBlob,这几个类型之间的唯一区别是在存储文件的最大大小上不同。
MySQL的四种BLOB类型
类型 大小(单位:字节)
TinyBlob 最大 255
  Blob 最大 65K
  MediumBlob 最大 16M
  LongBlob 最大 4G

如果要插入大数据量,需要修改mysql的配置文件【linux修改etc/my.cnf】:
[mysqld]
max_allowed_packet = 16M

将max_allowed_packet改成合适的值,具体此变量的解释请参照:http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

mysql截取字符串转成数字并比较排序

 SUBSTRING_INDEX([列名],[分割符],[段数])
 列名:要分割列里内容的列名
 分割符:用来切割的符号
 段数:切割后取的长度
select showids,SUBSTRING_INDEX(showids,',',-1) as x from cbd_products 
order by x asc

select showids from cbd_products  order by (SUBSTRING_INDEX(showids,',',-1)+0) asc

mysql特性(+0会自动转化为数字),作数值的大小比较!
tp5中的应用: $list=$obj->db->where($tj)->order("(SUBSTRING_INDEX(showids,',',-1)+0) asc,id desc")->select();

参考url:https://www.cnblogs.com/cuiyf/p/10077707.html

其他:mysql的查询中怎么截取字符串:

从左开始截取字符串left(str, length);

从右开始截取字符串right(str, length);

截取字符串substring(str, pos, length),说明:substring(被截取字段,从第几位开始截取,截取长度);

按关键字截取字符串substring_index(str,delim,count),说明:substring_index(被截取字段,关键字,关键字出现的次数)。

MySQL 数据库直接复制文件,InnoDB类型表不能用解决方案

不知道各位小伙伴有没有注意了,项目搬迁中,直接拷贝InnoDB的表文件不可用问题。解决过程如下:

一、 把data下的相关库文件拷贝至目的文件目录下;

二、再把data目录下的ibdata1拷贝至目的目录;

数据库运行ok!

总结:

如类型是MyISAM, 数据文件则以”Table.frm””Table.MYD””Table.MYI””三个文件存储于”/data/$databasename/”目录中.
如类型是InnoDB, 数据文件则存储在”$innodb_data_home_dir/″中的ibdata1文件中(一般情况),结构文件存在于table_name.frm中.
MySQL的数据库文件直接复制便可以使用,但是那是指“MyISAM”类型的表。

1:MyISAM类型的数据文件可以在不同操作系统中COPY,这点很重要,布署的时候方便点。(只需要拷贝 数据库名字文件夹下面的文件,这样数据库就拷贝完了)

2: InnoDB类型的 要注意多拷贝 ibdata1 , 最好不要是直接复制文件夹,而是应该用sql导入导出

整理:徐多蔚  xuduowei.com

宝塔面板mysql怎么用mysql-front或者navicat 连接数据库呢

因为不是很习惯phpmyadmin,所以在本地使用了Navicat进行数据库远程连接,但是在连接数据库的时候出现了各种问题。因为从宝塔后台进行登录的时候,是直接进入数据库的,宝塔的phpmyadmin的登录端口是888,然后就在登录的时候将端口改为888,后来尝试登录的时候,数据库账号密码都是正确的,却提示错误信息无法登录。这里大家不要被phpmyadmin的登录端口888所迷惑了,这只是登录phpmyadmin进入数据库的端口,并不是MySQL的端口,MySQL的端口依然是3306,这个时候将Navicat中的端口改为3306即可。

今天想使用mysql-front 连接 宝塔服务器的其中一个虚拟主机的mysql数据库,发现填写信息,端口填写了3306,无法链接,看了很多文章,都是无效,一概而论,对新手误解太大。下面具体就解决步骤。

第一步:打开阿里云服务器ECS,配置安全组放开3306端口。【一般情况下,这个不同操作,因为服务器都默认开启了这个端口】。

第二步:宝塔 找到 安全 这个菜单。开放3306端口。

第三步:宝塔环境->数据库->对应的用户权限,选择全部用户。

最后就可以用此用户开心愉快的链接了。

 

 

mysql获取某个表的所有字段名

mysql安装成功后可以看到已经存在mysql、information_schema和test这个几个数据库,information_schema库中有一个名为COLUMNS的表,这个表中记录了数据库中所有表的字段信息。知道这个表后,获取任意表的字段就只需要一条select语句即可。例如:

select COLUMN_NAME from information_schema.COLUMNS where table_name = 'your_table_name';   (自测有效)

上述的做法有一点问题,如果多个数据库中存在你想要查询的表名,那么查询的结果会包括全部的字段信息。通过DESC information_schema.COLUMNS可以看到该表中列名为TABLE_SCHEMA是记录数据库名,因此下面的写法更为严格

select COLUMN_NAME from information_schema.COLUMNS where table_name = 'your_table_name' and table_schema = 'your_db_name';  

 

探讨:mysql 数据库存时间最好是时间戳还是格式的时间

虽然我认为讨论技术问题时,宣扬“自己的系统实际表现有多牛(所以别人也一定要这么做)”不是什么好的论调……但我必须认同一点:这个问题的权衡还真就不是从性能上考虑的时间戳和字面时间的互转只是简单的计算,所消耗的资源远远达不到引发问题的地步。

使用时间戳的唯一考虑是:你的应用是否涉及多时区,时间数据是否和时区相关。如果回答“是”,那么就必须使用时间戳,没有任何第二方案。

只有时间戳表示的时间是准确、恒定的,就连时间+日期+时区也不行——时区这玩意儿可不是恒定不变的……

其余的都不是什么重要的考虑,自己喜欢就行。

一般认为坚持使用时间戳总是好的,在程序设计中只会提供便利,不会引入坏处。至于查看数据时暴露时间戳原值,那是显示环节的不完备(或故意设计),而不是用时间戳用错了,切勿张冠李戴抹黑好东西。

日期的字符串-时间互转、计算、比较及时区转换,请使用后台语言中提供的相关类,不自己造轮子就可以。

可以略微注意:2038年问题的陷阱。对于MySQL而言,如果存时间戳请使用bigint,而尽量不要使用int;若存时间是字符串型的,建议使用TIMESTAMP或者DATETIME。

TIMESTAMP与DATETIME2者的区别:

TIMESTAMP和DATETIME的相同点:

1> 两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期。

 

TIMESTAMP和DATETIME的不同点:

1> 两者的存储方式不一样

对于TIMESTAMP,它把客户端插入的时间从当前时区转化为UTC(世界标准时间)进行存储。查询时,将其又转化为客户端当前时区进行返回。

而对于DATETIME,不做任何改变,基本上是原样输入和输出。

2> 两者所能存储的时间范围不一样

timestamp所能存储的时间范围为:'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'。

datetime所能存储的时间范围为:'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。

 

总结:TIMESTAMP和DATETIME除了存储范围和存储方式不一样,没有太大区别。当然,对于跨时区的业务,TIMESTAMP更为合适。

 

https://www.cnblogs.com/mxwz/p/7520309.html

转载:http://www.360doc.com/content/16/0614/06/9200790_567583079.shtml

mysql创建库,表,列指令

编写整理:徐多蔚

创建数据库:
#nihaoma就是我们的数据库名称
create database nihaoma;

#删除库
 drop database nihaoma;
#查看所有的数据库
show databases;

#设置当前要操作的数据库
use nihaoma;

#列表查看指定库下的所有的表
show tables;

#若没表,我们可以演示创建;
CREATE TABLE `obj_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` char(32) NOT NULL DEFAULT '',
  `addtime` datetime DEFAULT NULL COMMENT '添加时间',
  `updatetime` datetime DEFAULT NULL,
  `gid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM AUTO_INCREMENT=56 DEFAULT CHARSET=utf8 COMMENT='用户表';

#命令行下查看表结构
desc obj_users;

#增加字段指令 tc004_tp5为数据库名,obj_users为名
ALTER TABLE `tc004_tp5`.`obj_users`
ADD COLUMN `cs4` varchar(255) NULL DEFAULT NULL COMMENT '测试';

#注意cmd命令下若输入中文的备注,有可能会出现乱码。

#修改字段(cs4中的备注为csssss)指令
ALTER TABLE `tc004_tp5`.`obj_users`
CHANGE COLUMN `cs4` `cs4` varchar(255) NULL DEFAULT NULL COMMENT 'csssss';

#删除字段cs4指令
ALTER TABLE `tc004_tp5`.`obj_users`
  DROP COLUMN `cs4`;

#删除表
 drop table obj_users;
#增,删,改,查sql语句这里省略。
……