最近想随手写一个简单的员工管理系统，第一次使用python连接数据库，在这个过程中就遇到了一些问题，遂记录 遇到问题习惯性百度一下，很多教程都不适合新手，有些还不知道是不是瞎写的，所以我觉得有必要自己写出来。上面的错误就是说这个port或者主机的值应该是非字符串，于是我试着在变量后面加上intport(int)=‘3309‘结果提示： 也就是这个语法是错误的，所以只能换一种方法。connect = pymysql.Connect(host=‘localhost‘...
MySQLdb._exceptions.OperationalError: (2026, ‘SSL connection error: unknown error number‘)
问题发生在我远程连接数据库的时候，我使用的是MySQLdb
但是一直报错
连接我自己的数据库是没有问题的
*原因是 python2下是使用的MySQLdb,python3下用的是pymysql *
问题可能是这个，我也不是很清楚
但是我用pymysql连接成功了
# 用pymysql连接尝试
import pymysql
# 连接数据库
conn = pymysql.connect(host = ‘‘,port = 3306,...
1.
SELECT t.*, @curRow := @curRow + 1 AS row_numberFROM structure tJOIN (SELECT @curRow := 0) r;
2.
set @x=0;SELECT @x :=ifnull(@x,0)+1 AS rownum ,order_snFROM order_item;mysql 方法row_number（）方法标签：本文系统来源：http://www.cnblogs.com/xiaowei1763369680/p/4930029.html
一. varchar2(10)和number应该转换为什么类型？
oracle转成mysql时：varchar2(10)可以转成varchar(10)number则要看oracle中存储的具体是什么类型的数据：1、如果是整型，那么mysql中，用int即可；2、如果是带小数位的，那么mysql中可用numeric类型。
注：mysql中没有varchar2(10)和number这两个数据类型
二. Mysql varchar VS Oracle varchar2
mysql和oracle做数据同步。其中表的一个字段在mysql中设置为varchar(6),Oracle中为va...
step1：查看1.1 Mysql命令行里输入“show engines；”查看innoddb数据引擎状态，1.2 show variables “%_buffer%”里查看innodb_buffer_pool_size的数值，默认是8M（太小，需要改大一点！）<img alt="技术分享" src="https://img.gxlcms.com/https://img.gxlcms.com//Uploads-s/new/2020-10-13-qlqqti/20180110191246001649.png" ;="" border="0" height="292" width="389" name="" style="word-wrap: break-word;">step2：找配置文...
TABLE `test` (`ID` int(11) NOT NULL AUTO_INCREMENT,`Col1` varchar(50) DEFAULT NULL,`Col2` varchar(50) DEFAULT NULL,`Col3` int(11) DEFAULT NULL,`Col4` float DEFAULT NULL,PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;然后插入测试数据，使用po主介绍的方法来测试insert into test(Col1,Col2,Col3,Col4) values (1,2,3,4),(1,2,6,5),(1,2,7,8),(1,"激发",5,7);delimiter //select @row:=c...
SELECT prod_province,prod_cnode,prod_balance,rank FROM ( SELECT b.prod_province,b.prod_cnode,b.prod_balance,@rownum:=@rownum+1 , IF(@pdept=b.prod_province,@rank:=@rank+1,@rank:=1) AS rank, @pdept:=b.prod_province FROM ( SELECT prod_province,prod_cnode,SUM(prod_balance) prod_balance FROM t_prod_info GROUP BY prod_province,prod_cnode ORDER BY prod_province,prod_balance DESC) b ,(SELECT @rownum :...
drop table if exists tmp1; create table tmp1 (empid int ,deptid int ,salary decimal(10,2) ); insert into tmp1 values (1,10,5500.00), (2,10,4500.00), (3,20,1900.00), (4,20,4800.00), (5,40,6500.00), (6,40,14500.00), (7,40,44500.00), (8,50,6500.00), (9,50,7500.00);
select empid,deptid,salary,rank from ( select a.empid,a.deptid,a.salary,@rownum:=@rownum+1 , if(@pdept=a.deptid,@rank:=@...
set @count = 0; set @num = 0; SELECT @count :=count(1) FROM better.MonitorInfo where itemId=d_itemId and configId=d_configId; IF @count<300 THEN SELECT id,cpu,cpu1,cpu2,cpu3,diskRead,diskWrite,memory,networkReceive,networkSend,time,configId,itemId FROM MonitorInfo where itemId=itemId and configId=configId; ELSE SET @num= round(@count/d_count_num,0); select @num; create temporary table tmp...
然而经过实测，该办法在表空时返回也为空。考虑到表空的情形，加一个集合，如下：
SELECT random_num
FROM (SELECT FLOOR(RAND() * 99999) AS random_num FROM numbersUNIONSELECT FLOOR(RAND() * 99999) AS random_num
) AS ss
WHERE "random_num" NOT IN (SELECT my_number FROM numbers)
LIMIT 1
通过union字段在表空时，直接获取一个随机数返回。
延伸：mysql随机取记录
基本思路：随机排序。
select * from numbers order by ...
with Result as(select SUM(F_DayValue) AS F_Value,F_ZZ_ttBuildID,F_EnergyItemCode from T_EC_EnergyItemDayResult where F_EnergyItemCode like ‘%000‘and F_StartDay>=@ldStartDate and F_StartDay<=@ldEndDateand F_ZZ_ttBuildID IN (select F_BuildID from T_BD_BuildBaseInfo)group by F_ZZ_ttBuildID,F_EnergyItemCode)select a.F_Value,a.F_ZZ_ttBuildID,b.F_BuildName,a.F_EnergyItemCode,ROW_NUMBER() over(p...
select ‘top3‘ as top,news_id,count(1) as counts from (SELECT news_id, pv, row_number () over ( PARTITION BY statis_date ORDER BY pv DESC ) row_numFROM author_pvWHERE statis_date = ‘${yesterday}‘ and settle_type=2)t1 where t1.row_num between 41 and 60 group by news_idmysql排序row_number标签：mys nts tis day sele from 排序 where log 本文系统来源：https://www.cnblogs.com/tq...
利用内联关系取出多组条数据最新的数据（即oracle的row_number概念）：
-- 筛选出的状态 inner join筛选出来的状态值select t.flow_id,t.job_id,t.`status`,t.start_timefrom (SELECT flow_id,job_id,status,start_time,max(FROM_UNIXTIME(start_time/1000)) as max_start_timefrom execution_jobs -- where (flow_id=‘end_it_sda‘ or flow_id=‘end_paasjszb_sda‘)where (flow_id=‘end_paasjszb_sda‘) and start_time>154497...
00:30:01 >> log scanned up to (88343615848)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
InnoDB: Operating system error number 24 in a file operation.
InnoDB: Error number 24 means ‘Too many open files‘
InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-co...
解决记录如下： 在mysql里面进行修改操作时提示：The total number of locks exceeds the lock table size ，通过百度搜到innodb_buffer_pool_size过小； 打开mysql 命令框 输入 show variables like "%tmp%";查看innodb_buffer_pool_size,输入SET GLOBAL innodb_buffer_pool_size=67108864;完成之后再次使用show variables like "%tmp%";查询，如下图。（这里不需要重启mysql服务，我这边重启之后size会还原大小还得再次修改...
