mysql 常见操作

创建唯一索引

alter table xxxx add unique(`a`, `b`);

python操作mysql

#encoding=utf8

import fcntl,json,redis,os,time,string, datetime
import MySQLdb
import MySQLdb.cursors
# https://mysqlclient.readthedocs.io/user_guide.html?msclkid=33043771baf611ecb6b4211e7a32067a#functions-and-attributes


MYSQL_HOST = os.environ.get('PUSH_MYSQL_HOST')
MYSQL_WRITER_USERNAME = os.environ.get('MYSQL_WRITER_USERNAME')
MYSQL_WRITER_PASSWORD = os.environ.get('MYSQL_WRITER_PASSWORD')
MYSQL_PORT = os.environ.get('MYSQL_PORT')

# charset 参数是设置connction中使用的编码，一般设置set names 就告诉服务器客户端和服务器使用utf8了
db=MySQLdb.connect(host=MYSQL_HOST,user=MYSQL_WRITER_USERNAME,\
	passwd=MYSQL_WRITER_PASSWORD,port=string.atoi(MYSQL_PORT),db='test',init_command="set names utf8" )
dbc = db.cursor(MySQLdb.cursors.DictCursor) # MySQLdb.cursors.DictCursor表示类型，返回字典形式

dbc.execute('insert test_1 set name="测试照顾捏了"')
dbc.execute('''insert test_1 set name="%s", id=%s''', ("xx","1")) #  元组一个元素要加逗号
dbc.executemany('''insert into test_1 (name) values(%s)''',[("1"),("2")]);
db.commit()

# sql中字段不需要加引号, 如果加，需要最外层3引号，里面双引号，不能单引号, 如果字段不是char，不可以加引号
dbc.execute('''select * from  test_1 where name="%s" and id=%s''', ("xx",100)) #("xx", 1) 都行
#只有一个参数需要元组要加 逗号
dbc.execute('select * from  test_1 where name=%s', ("xx",))
x = dbc.fetchone()
# dbc.fetchmany(1) 获取几条
data = dbc.fetchall()
for x in data:
	print "for", x
	
db.close()

php操作mysql

date_default_timezone_set("Asia/Shanghai");
echo "=========================start: ".date("Y-m-d H:i:s", time())."============\n";


function mysql_init()
{
    $host = getenv('GARDEN_MYSQL_HOST');
    $dsn = "mysql:host=$host;dbname=garden";
    $user = getenv('MYSQL_WRITER_USERNAME');
    $pass = getenv('MYSQL_WRITER_PASSWORD');

    $mysql = new PDO($dsn, $user, $pass);
    $mysql->query("set names utf8");

    return $mysql;
}


// query 
$mysql = mysql_init("test");
$sql = "select * from test limit 10;";
$mysql->query($sql);

// write 
$mysql->exec("insert into test set a =10");

// 先准备，一般查询大量数据
$mysql_query->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); # 不实用本地缓存，避免一次性把数据都取到本地占内存
$mysql->prepare($sql);
$mysql->execute();
while($row = $sth->fetch(PDO::FETCH_ASSOC)){
    print_r($row);
}
// 已经设置了属性，直接query，也可以吧，

$mysql = null;

go操作mysql

package main

import(
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
"fmt"
)

// 全局变量
var (
	userName  string = "test"
	password  string = "test@231"
	ipAddrees string = os.Getenv("MYSQL_HOST")
	port      int    = 3306
	dbName    string = "testdb"
	charset   string = "utf8"
)

func connectMysql() (*sqlx.DB) {
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", 
						userName, password, ipAddrees, port, dbName, charset)
	Db, err := sqlx.Open("mysql", dsn)
	Db.SetMaxOpenConns(200) 
	Db.SetMaxIdleConns(100)
	if err != nil {
		fmt.Printf("mysql connect failed, detail is [%v]", err.Error())
	}
	return Db
}

var Db *sqlx.DB = connectMysql()
defer Db.Close()
rows, err := Db.Query("select * from test where _id = ? and appid = ?", id, appid)
for rows.Next() {
    var id, appid int
    err := rows.Scan(&id, &appid)
    if err != nil {
 	fmt.Println("Line 328, get data failed, error:[%v]", err.Error())
    } else {
        fmt.Println(id, appid)
    }

}
	

_, err := Db.Exec("insert into test (id, appid) values(?,?)", id, appid)

if err != nil {
	fmt.Printf("Insert error")
	os.Exit(1)
}
	

openresty-lua操作mysql

local mysql = require "resty.mysql"

function _M.connectDB4Write()
	local conn = mysql:new() -- return a conntion object
	conn:set_timeout(5000)
	--conn:set_keepalive(10000, 100) 
	local ok, err, errno, sqlstate = conn:connect{
		host = DBconfig.writer.HOST,
		port = DBconfig.PORT,
		database = DBconfig.DATABASE,
		user = DBconfig.writer.username,
		password = DBconfig.writer.password,
		max_packet_size = 1024 * 1024 }
	if not ok then
		return false,err,errno,sqlstate
	else
		return conn,nil
	end
end


function _M.closeDB(conn)
	if conn then
		conn:set_keepalive(10000,100)
	end
end