#encoding:utf-8
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="数据库用户名",
password="数据库密码",
database="数据库名"
)
mycursor = mydb.cursor()
mycursor.execute("SHOW TABLES")
tables = mycursor.fetchall()
for table in tables:
table_name = table[0]
#mycursor.execute(f"USE {table_name}") # 使用指定的表所在的数据库
mycursor.execute(f"SHOW COLUMNS FROM {table_name}") # 获取表的所有列信息
columns = mycursor.fetchall()
for column in columns:
column_name = column[0]
sql = f"UPDATE {table_name} SET {column_name} = REPLACE({column_name}, '旧字符串', '新字符串')"
mycursor.execute(sql)
mydb.commit()
mycursor.close()
mydb.close()
Linux上Java编译启动
#!/bin/bash
#读取lib文件夹的jar库
FILES=$(ls ./lib/*.jar)
JAVA_CP="."
for f in $FILES
do
JAVA_CP="$JAVA_CP:$f"
done
if [ ! -f 类名.class ]
then
#编译
javac -cp $JAVA_CP 类名.java
fi
#启动
java -cp $JAVA_CP 类名Java 下载远程图片到本地
……………………
public static void downloadImg(String imgUrl,String fileName) {
final String DocumentName = fileName;
final String DocumentPath = "imgs"+File.separator;
final String DownloadURL = imgUrl;
try {
//自动创建文件夹
File file = new File(DocumentPath);
if (!file.exists() && !file.isDirectory()) {
file.mkdirs();
}
//解析下载地址
URL url = new URL(DownloadURL);
URLConnection cnt = url.openConnection();
InputStream inStream = cnt.getInputStream();
FileOutputStream fos = new FileOutputStream(DocumentPath + DocumentName);
int bytesum = 0;
int byteread;
byte[] buffer = new byte[1204];
while ((byteread = inStream.read(buffer)) != -1) {
bytesum += byteread;
fos.write(buffer, 0, byteread);
}
} catch (IOException e) {
e.printStackTrace();
}
}
……………………PHP 调用file_get_contents或curl等方法获取接口数据返回SSL错误的问题解决方法
找到PHP配置文件，可用以下BASH命令搜索具体位置
find / -name "php.ini"编辑php.ini文件
vim /usr/local/php/etc/php.ini修改配置文件
找到;openssl.cafile=
去掉注释（即去掉前面的分号）
在等号后面添加受信任的根证书路径（根证书文件见本文附件）
最终效果如下（根据实际情况自行修改）：#####
openssl.cafile=/usr/local/php5.6/etc/cacert.pem
#####保存后重启相关服务
根证书下载
Java 解析URL参数
……………………
public static String getUrlparameter(String url, String name) {
url += "&";
String pattern = "(\\?|&){1}#{0,1}" + name + "=[a-zA-Z0-9]*(&{1})";
Pattern r = Pattern.compile(pattern);
Matcher matcher = r.matcher(url);
if (matcher.find()) {
return matcher.group(0).split("=")[1].replace("&", "");
} else {
return null;
}
}
……………………Linux上docker方式运行onlyoffice
启动
docker run -i -t -d -p 90:80 --restart=always -v /onlyoffice/log:/var/log/onlyoffice -v /onlyoffice/data:/var/www/onlyoffice/Data -v /onlyoffice/lib:/var/lib/onlyoffice -v /onlyoffice/db:/var/lib/postgresql onlyoffice/documentserver
获取ID
docker ps获取密钥
docker exec $1 /var/www/onlyoffice/documentserver/npm/json -f /etc/onlyoffice/documentserver/local.json 'services.CoAuthoring.secret.session.string'
Java 本地文件上传FTP服务器
……………………
public static void uploadFtp(String url, int port, String username, String password, String remoteFilePath, String localFilePath){
FTPClient ftpClient = new FTPClient();
File localFile = new File(localFilePath);
try {
ftpClient.connect(url, port);
ftpClient.login(username, password);
ftpClient.enterLocalPassiveMode();
ftpClient.setFileType(FTP.BINARY_FILE_TYPE);
FileInputStream inputStream = new FileInputStream(localFile);
ftpClient.storeFile(remoteFilePath, inputStream);
inputStream.close();
ftpClient.logout();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (ftpClient.isConnected()) {
try {
ftpClient.disconnect();
} catch (IOException e) {
e.printStackTrace();
}
}
if (localFile.exists()) {
if(!localFile.delete()){
//System.out.println("本地文件删除失败，请手动删除imgs文件夹内的全部文件");
}
}
}
}
……………………Java 修改图片尺寸
……………………
public static void compressImg(String filePath, String ext) throws Exception {
int newWidth = 160;
int newHeight = 160;
File imgFile = new File(filePath);
BufferedImage image = ImageIO.read(imgFile);
int width = image.getWidth();
int height = image.getHeight();
if(Math.abs(width-height)==0){
BufferedImage newImage = new BufferedImage(newWidth, newHeight, image.getType());
Graphics2D g = newImage.createGraphics();
g.drawImage(image, 0, 0, newWidth, newHeight, null);
g.dispose();
ImageIO.write(newImage, ext, new File(filePath));
}
}
……………………PHP 微信支付退款-单文件
<?php
/**
* 关于微信退款的说明
* 1.微信退款要求必传证书，需要到https://pay.weixin.qq.com 账户中心->账户设置->API安全->下载证书，证书路径在第119行和122行修改
* 2.错误码参照 ：https://pay.weixin.qq.com/wiki/doc/api/jsapi.php?chapter=9_4
*/
header('Content-type:text/html; Charset=utf-8');
$mchid = ''; //微信支付商户号 PartnerID 通过微信支付商户资料审核后邮件发送
$appid = ''; //微信支付申请对应的公众号的APPID
$apiKey = ''; //https://pay.weixin.qq.com 帐户设置-安全设置-API安全-API密钥-设置API密钥
$orderNo = ''; //商户订单号（商户订单号与微信订单号二选一，至少填一个）
$wxOrderNo = ''; //微信订单号（商户订单号与微信订单号二选一，至少填一个）
$totalFee = 0.20; //订单金额，单位:元
$refundFee = 0.20; //退款金额，单位:元
$refundNo = 'refund_'.uniqid(); //退款订单号(可随机生成)
$wxPay = new WxpayService($mchid,$appid,$apiKey);
$result = $wxPay->doRefund($totalFee, $refundFee, $refundNo, $wxOrderNo,$orderNo);
if($result===true){
echo 'refund success';exit();
}
echo 'refund fail';
class WxpayService
{
protected $mchid;
protected $appid;
protected $apiKey;
public $data = null;
public function __construct($mchid, $appid, $key)
{
$this->mchid = $mchid; //https://pay.weixin.qq.com 产品中心-开发配置-商户号
$this->appid = $appid; //微信支付申请对应的公众号的APPID
$this->apiKey = $key; //https://pay.weixin.qq.com 帐户设置-安全设置-API安全-API密钥-设置API密钥
}
/**
* 退款
* @param float $totalFee 订单金额 单位元
* @param float $refundFee 退款金额 单位元
* @param string $refundNo 退款单号
* @param string $wxOrderNo 微信订单号
* @param string $orderNo 商户订单号
* @return string
*/
public function doRefund($totalFee, $refundFee, $refundNo, $wxOrderNo='',$orderNo='')
{
$config = array(
'mch_id' => $this->mchid,
'appid' => $this->appid,
'key' => $this->apiKey,
);
$unified = array(
'appid' => $config['appid'],
'mch_id' => $config['mch_id'],
'nonce_str' => self::createNonceStr(),
'total_fee' => floatval($totalFee) * 100, //订单金额 单位 转为分
'refund_fee' => floatval($refundFee) * 100, //退款金额 单位 转为分
'sign_type' => 'MD5', //签名类型 支持HMAC-SHA256和MD5，默认为MD5
'transaction_id'=>$wxOrderNo, //微信订单号
'out_trade_no'=>$orderNo, //商户订单号
'out_refund_no'=>$refundNo, //商户退款单号
'refund_desc'=>'商品已售完', //退款原因（选填）
);
$unified['sign'] = self::getSign($unified, $config['key']);
$responseXml = $this->curlPost('https://api.mch.weixin.qq.com/secapi/pay/refund', self::arrayToXml($unified));
$unifiedOrder = simplexml_load_string($responseXml, 'SimpleXMLElement', LIBXML_NOCDATA);
if ($unifiedOrder === false) {
die('parse xml error');
}
print_r($unifiedOrder);
if ($unifiedOrder->return_code != 'SUCCESS') {
die($unifiedOrder->return_msg);
}
if ($unifiedOrder->result_code != 'SUCCESS') {
die($unifiedOrder->err_code);
}
return true;
}
public static function curlGet($url = '', $options = array())
{
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_TIMEOUT, 30);
if (!empty($options)) {
curl_setopt_array($ch, $options);
}
//https请求 不验证证书和host
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
$data = curl_exec($ch);
curl_close($ch);
return $data;
}
public function curlPost($url = '', $postData = '', $options = array())
{
if (is_array($postData)) {
$postData = http_build_query($postData);
}
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $postData);
curl_setopt($ch, CURLOPT_TIMEOUT, 30); //设置cURL允许执行的最长秒数
if (!empty($options)) {
curl_setopt_array($ch, $options);
}
//https请求 不验证证书和host
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
//第一种方法，cert 与 key 分别属于两个.pem文件
//默认格式为PEM，可以注释
curl_setopt($ch,CURLOPT_SSLCERTTYPE,'PEM');
curl_setopt($ch,CURLOPT_SSLCERT,getcwd().'/apiclient_cert.pem');
//默认格式为PEM，可以注释
curl_setopt($ch,CURLOPT_SSLKEYTYPE,'PEM');
curl_setopt($ch,CURLOPT_SSLKEY,getcwd().'/apiclient_key.pem');
//第二种方式，两个文件合成一个.pem文件
// curl_setopt($ch,CURLOPT_SSLCERT,getcwd().'/all.pem');
$data = curl_exec($ch);
curl_close($ch);
return $data;
}
public static function createNonceStr($length = 16)
{
$chars = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
$str = '';
for ($i = 0; $i < $length; $i++) {
$str .= substr($chars, mt_rand(0, strlen($chars) - 1), 1);
}
return $str;
}
public static function arrayToXml($arr)
{
$xml = "<xml>";
foreach ($arr as $key => $val) {
if (is_numeric($val)) {
$xml .= "<" . $key . ">" . $val . "</" . $key . ">";
} else
$xml .= "<" . $key . "><![CDATA[" . $val . "]]></" . $key . ">";
}
$xml .= "</xml>";
return $xml;
}
public static function getSign($params, $key)
{
ksort($params, SORT_STRING);
$unSignParaString = self::formatQueryParaMap($params, false);
$signStr = strtoupper(md5($unSignParaString . "&key=" . $key));
return $signStr;
}
protected static function formatQueryParaMap($paraMap, $urlEncode = false)
{
$buff = "";
ksort($paraMap);
foreach ($paraMap as $k => $v) {
if (null != $v && "null" != $v) {
if ($urlEncode) {
$v = urlencode($v);
}
$buff .= $k . "=" . $v . "&";
}
}
$reqPar = '';
if (strlen($buff) > 0) {
$reqPar = substr($buff, 0, strlen($buff) - 1);
}
return $reqPar;
}
}
?>
PHP 日志类
<?php
class Libs_Log_Logger {
const TRANSFER_ERROR = 'LOG转换失败，非LOG信息';
const LOG_FILENAME = 'output.log';
/**
* 本函数用于取代系统的error_log错误日志记录函数
* @param string|array|object|bool|int $log 需要记录的错误信息，可以是格式良好的任意类型
* @param string $filename 保存日志信息的文件名，默认存储路径为sites/app/storage/log/output.log
* @param bool $append 是否以追加的形式添加日志信息，默认追加
*/
public static function outputLog($log, $filename = self::LOG_FILENAME, $append = true) {
if (!plum_setmod_dir(PLUM_APP_LOG)) {
return;
}
$filename = PLUM_APP_LOG . '/' . $filename;
$mode = $append ? 'a' : 'w';
if (!($handler = @fopen($filename, $mode))) {
//文件流打开失败时触发错误，并退出
trigger_error('LOG日志文件打开失败', E_USER_WARNING);
return;
}
//获取调用跟踪
$trace = debug_backtrace(DEBUG_BACKTRACE_PROVIDE_OBJECT, 1);
$debug = self::log_format(array('file' => $trace[0]['file'], 'line' => $trace[0]['line']));
$info = self::log_format($log);
//开始写入
if (($write_byte = fwrite($handler, $debug.$info)) === false) {
$error_msg = 'LOG日志写入失败';
//检查磁盘空间
$free_space = disk_free_space(PLUM_APP_LOG);
//磁盘空间不足
if ($free_space && $write_byte > $free_space) {
$error_msg .= '磁盘空间不足';
}
//写入失败时，触发错误并退出
trigger_error($error_msg, E_USER_WARNING);
fclose($handler);
return;
}
//关闭文件流
fclose($handler);
}
/**
* 输出信息到控制台
*/
public static function outputConsoleLog($log) {
//非控制台调用直接退出
if (php_sapi_name() != 'cli') {
//die('请在控制台下执行脚本');
return;
}
$info = self::log_format($log);
fwrite(STDOUT, $info);
}
/**
* 返回格式化的LOG日志信息
* @param $log
* @return string
*/
private static function log_format($log) {
$log_type = 'Unknown';
if (is_array($log)) {
//数组进行json序列化
$log_type = 'Array';
$loginfo = self::json_encode_helper($log);
} else if (is_object($log)) {
$log_type = 'Object';
$loginfo = self::json_encode_helper($log);
} else if (is_string($log)) {
$log_type = 'String';
$loginfo = $log;
} else if (is_bool($log)) {
$log_type = 'Bool';
$loginfo = $log ? 'true' : 'false';
} else if (is_numeric($log)) {
$log_type = 'Number';
$loginfo = strval($log);
} else if (is_null($log)) {
$log_type = 'Null';
$loginfo = 'null';
} else {
$loginfo = strval($log);
}
//再一次判断，排查转换出错的情况
if (!is_string($loginfo)) {
$errno = json_last_error();
switch ($errno) {
default :
$loginfo = self::TRANSFER_ERROR.";错误号：{$errno};错误信息：".json_last_error_msg();
break;
}
}
//添加换行符
$loginfo .= "\n";
$format = "[".date('Y/m/d H:i:s', time())."] $log_type: $loginfo";
return $format;
}
/*
* utf8格式转换
*/
private static function utf8ize( $mixed ) {
if (is_array($mixed)) {
foreach ($mixed as $key => $value) {
$mixed[$key] = self::utf8ize($value);
}
} elseif (is_string($mixed)) {
//$mixed = mb_convert_encoding($mixed, "UTF-8", "UTF-8");
$mixed = utf8_encode($mixed);
} elseif (is_object($mixed)) {
foreach ($mixed as $key => $value) {
$mixed->$key = self::utf8ize($value);
}
}
return $mixed;
}
private static function json_encode_helper($var) {
switch (gettype($var)) {
case 'boolean':
return $var ? 'true' : 'false'; // Lowercase necessary!
case 'integer':
case 'double':
return $var;
case 'resource':
case 'string':
// Always use Unicode escape sequences (\u0022) over JSON escape
// sequences (\") to prevent browsers interpreting these as
// special characters.
$replace_pairs = array(
// ", \ and U+0000 - U+001F must be escaped according to RFC 4627.
'\\' => '\u005C',
'"' => '\u0022',
"\x00" => '\u0000',
"\x01" => '\u0001',
"\x02" => '\u0002',
"\x03" => '\u0003',
"\x04" => '\u0004',
"\x05" => '\u0005',
"\x06" => '\u0006',
"\x07" => '\u0007',
"\x08" => '\u0008',
"\x09" => '\u0009',
"\x0a" => '\u000A',
"\x0b" => '\u000B',
"\x0c" => '\u000C',
"\x0d" => '\u000D',
"\x0e" => '\u000E',
"\x0f" => '\u000F',
"\x10" => '\u0010',
"\x11" => '\u0011',
"\x12" => '\u0012',
"\x13" => '\u0013',
"\x14" => '\u0014',
"\x15" => '\u0015',
"\x16" => '\u0016',
"\x17" => '\u0017',
"\x18" => '\u0018',
"\x19" => '\u0019',
"\x1a" => '\u001A',
"\x1b" => '\u001B',
"\x1c" => '\u001C',
"\x1d" => '\u001D',
"\x1e" => '\u001E',
"\x1f" => '\u001F',
// Prevent browsers from interpreting these as as special.
"'" => '\u0027',
'<' => '\u003C',
'>' => '\u003E',
'&' => '\u0026',
// Prevent browsers from interpreting the solidus as special and
// non-compliant JSON parsers from interpreting // as a comment.
//'/' => '\u002F',
// While these are allowed unescaped according to ECMA-262, section
// 15.12.2, they cause problems in some JSON parsers.
"\xe2\x80\xa8" => '\u2028', // U+2028, Line Separator.
"\xe2\x80\xa9" => '\u2029', // U+2029, Paragraph Separator.
);
return '"' . strtr($var, $replace_pairs) . '"';
case 'array':
// Arrays in JSON can't be associative. If the array is empty or if it
// has sequential whole number keys starting with 0, it's not associative
// so we can go ahead and convert it as an array.
if (empty($var) || array_keys($var) === range(0, sizeof($var) - 1)) {
$output = array();
foreach ($var as $v) {
$output[] = self::json_encode_helper($v);
}
return '[ ' . implode(', ', $output) . ' ]';
}
// Otherwise, fall through to convert the array as an object.
case 'object':
$output = array();
foreach ($var as $k => $v) {
$output[] = self::json_encode_helper(strval($k)) . ':' . self::json_encode_helper($v);
}
return '{' . implode(', ', $output) . '}';
default:
return 'null';
}
}
}