Contents [hide]
输出结果
Result can be matched with following regex expression.
1 | r"1\tname1\tmale\t\d+/\d+/\d+ \d+:\d+:\d+\t\d+/\d+/\d+ \d+:\d+:\d+\n2\tname2\tfemale\t\d+/\d+/\d+ \d+:\d+:\d+\t\d+/\d+/\d+ \d+:\d+:\d+$" |
结果示例
1 2 | 1 name1 male 2019/10/29 18:04:45 2019/10/29 18:04:45 2 name2 female 2019/10/29 18:04:45 2019/10/29 18:04:45 |
先决条件
Prepare .env file storing db connection information like this.
1 2 3 | DBHOST=0.0.0.0 DBUSER=exampleuser DBPASSWORD=example20141024 |
执行以下sql并为数据库和用户准备脚本
1 2 3 4 5 6 7 8 9 | CREATE USER 'exampleuser'@'localhost' IDENTIFIED BY 'example20141024'; CREATE USER 'exampleuser'@'10.%' IDENTIFIED BY 'example20141024'; CREATE USER 'exampleuser'@'172.%' IDENTIFIED BY 'example20141024'; CREATE USER 'exampleuser'@'192.%' IDENTIFIED BY 'example20141024'; CREATE DATABASE example CHARACTER SET utf8mb4; GRANT ALL PRIVILEGES ON example.* TO 'exampleuser'@'localhost'; GRANT ALL PRIVILEGES ON example.* TO 'exampleuser'@'10.%'; GRANT ALL PRIVILEGES ON example.* TO 'exampleuser'@'172.%'; GRANT ALL PRIVILEGES ON example.* TO 'exampleuser'@'192.%'; |
程式码
JavaScript
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 | class MysqlManipulator { constructor(opt={}) { this .mysql = require( 'mysql' ); require( 'dotenv' ).config(); this .rdbh = this .mysql.createConnection({ host: process.env.DBHOST, user: process.env.DBUSER, password: process.env.DBPASSWORD, database: 'example' }); } async beginTransaction() { const sql = "BEGIN" await this .executeSQL(sql) } async createExampleData() { const datas = [ [ 'name1' , 'male' ], [ 'name2' , 'female' ] ] const sql = 'INSERT INTO example (name, sex) VALUES (?, ?)' for (const data of datas) { const res = await this .rdbh.query(sql, data) } } async createExampleTable() { const sql = `CREATE TABLE example ( id int NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, sex ENUM( 'male' , 'female' ) DEFAULT NULL, created_at timestamp NOT NULL DEFAULT NOW(), updated_at timestamp NOT NULL DEFAULT NOW(), PRIMARY KEY (id), KEY created_at_idx (created_at), KEY name_idx (name), KEY updated_at_idx (updated_at) );`; await this .executeSQL(sql); } async dbclose() { if ( typeof ( this .rdbh) !== "undefined" ) { await this .rdbh.end(); } } async dbconnect() { await this .rdbh.connect(); const timezone = "Asia/Tokyo" ; const sql = "SET TIME_ZONE = '" + timezone + "'" await this .executeSQL(sql) } async dropExampleTable() { const sql = "DROP TABLE IF EXISTS example" ; await this .executeSQL(sql); } async endTransaction() { await this .rdbh.commit() } async executeSQL(sql) { try { const res = await this .rdbh.query(sql); } catch (err){ const sql = "ROLLBACK" await this .executeSQL(sql) console.log(err.name + ': ' + err.message); } } async selectFromExampleTable() { const sql = "SELECT id, name, sex, DATE_FORMAT(created_at, '%Y/%m/%d %H:%i:%S') AS created_at, DATE_FORMAT(updated_at, '%Y/%m/%d %H:%i:%S') AS updated_at FROM example" ; try { await this .rdbh.query(sql, function (err, rows, fields) { if (err) { console.log( 'err: ' + err); } for (const row of rows){ console.log([row.id, row.name, row.sex, row.created_at, row.updated_at].join( "\t" )) } }); } catch (err) { const sql = "ROLLBACK" await this .executeSQL(sql) console.log(err.name + ': ' + err.message) process.exit(-1) } } }; module.exports = MysqlManipulator; if (!module.parent) { ( async () => { const rdb = new MysqlManipulator() await rdb.dbconnect() await rdb.beginTransaction() await rdb.dropExampleTable() await rdb.createExampleTable() await rdb.createExampleData() await rdb.selectFromExampleTable() await rdb.dropExampleTable() await rdb.endTransaction() await rdb.dbclose() })() } |
Perl
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 | package MysqlManipulator; use strict; use DBI; use Dotenv; sub new() { my $self = {}; Dotenv->load(); return bless $self ; } sub beginTransaction(){ my $self = shift ; my $sql = "BEGIN" ; $self ->executeSQL( $sql ); } sub createExampleData(){ my $self = shift ; my @datas = ( [ 'name1' , 'male' ], [ 'name2' , 'female' ] ); my $sth = $self ->{ 'rdbh' }->prepare( "INSERT INTO example (name, sex) VALUES (?, ?)" ); foreach my $data_ref ( @datas ) { $sth ->execute( $data_ref ->[0], $data_ref ->[1]); } $sth ->finish(); } sub createExampleTable() { my $self = shift ; my $sql = <<EOF; CREATE TABLE example ( id int NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, sex ENUM('male', 'female') DEFAULT NULL, created_at timestamp NOT NULL DEFAULT NOW(), updated_at timestamp NOT NULL DEFAULT NOW(), PRIMARY KEY (id), KEY `created_at_idx` (`created_at`), KEY `name_idx` (`name`), KEY `updated_at_idx` (`updated_at`) ); EOF $self ->executeSQL( $sql ); } sub dbclose(){ my $self = shift ; $self ->{ 'rdbh' }->disconnect(); } sub dbconnect(){ my $self = shift ; my $database = "example" ; my $timezone = "Asia/Tokyo" ; my $dbi = 'mysql' ; $self ->{ 'rdbh' } = DBI-> connect ( "DBI:" . $dbi . ":database=" . $database . ";host=" . $ENV { 'DBHOST' }, $ENV { 'DBUSER' }, $ENV { 'DBPASSWORD' }, { AutoCommit => 0, PrintError => 1, PrintWarn => 0 }) or die "Failed to connect to db." ; my $sql = "SET TIME_ZONE='" . $timezone . "'" ; $self ->{ 'rdbh' }-> do ( $sql ); } sub dropExampleTable(){ my $self = shift ; my $sql = "DROP TABLE IF EXISTS example" ; $self ->executeSQL( $sql ); } sub endTransaction(){ my $self = shift ; $self ->{ 'rdbh' }->commit(); } sub executeSQL(){ my $self = shift ; my $sql = shift ; $self ->{ 'rdbh' }-> do ( $sql ) or die ( "Failed to execute " . $sql ); } sub selectFromExampleTable(){ my $self = shift ; my $sql = "SELECT id, name, sex, DATE_FORMAT(created_at, '%Y/%m/%d %H:%i:%S') AS created_at, DATE_FORMAT(updated_at, '%Y/%m/%d %H:%i:%S') AS updated_at FROM example" ; my $sth = $self ->{ 'rdbh' }->prepare( $sql ) or die ( "Failed to prepare " . $sql ); $sth ->execute() or die ( "Failed to execute " . $sql ); while ( my $row_ref = $sth ->fetchrow_hashref) { print ( join ( "\t" , $row_ref ->{ 'id' }, $row_ref ->{ 'name' }, $row_ref ->{ 'sex' }, $row_ref ->{ 'created_at' }, $row_ref ->{ 'updated_at' }). "\n" ); } $sth ->finish(); } if ($0 eq __FILE__) { my $rdb = new MysqlManipulator(); $rdb ->dbconnect(); $rdb ->beginTransaction(); $rdb ->dropExampleTable(); $rdb ->createExampleTable(); $rdb ->createExampleData(); $rdb ->selectFromExampleTable(); $rdb ->dropExampleTable(); $rdb ->endTransaction(); $rdb ->dbclose(); } else { 1; } |
PHP
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 | <?php require_once __DIR__ . '/vendor/autoload.php' ; class MysqlManipulator { private $rdbh ; public function __construct( $opt =[]) { $dotenv = Dotenv\Dotenv::create(__DIR__); $dotenv ->load(); } function beginTransaction() { $this ->rdbh->beginTransaction(); } function createExampleData(){ $datas = [ [ 'name1' , 'male' ], [ 'name2' , 'female' ] ]; $sql = "INSERT INTO example (name, sex) VALUES (?, ?)" ; $sth = $this ->rdbh->prepare( $sql ); foreach ( $datas as $data ) { $sth ->execute([ $data [0], $data [1]]); } } function createExampleTable(){ $sql = <<< EOF CREATE TABLE example ( id int NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, sex ENUM( 'male' , 'female' ) DEFAULT NULL, created_at timestamp NOT NULL DEFAULT NOW(), updated_at timestamp NOT NULL DEFAULT NOW(), PRIMARY KEY (id), KEY `created_at_idx` (`created_at`), KEY `name_idx` (`name`), KEY `updated_at_idx` (`updated_at`) ); EOF; $this ->executeSQL( $sql ); } function dbclose() { $this ->rdbh = null; } function dbconnect(){ $dbname = "example" ; $timezone = "Asia/Tokyo" ; try { $this ->rdbh = new PDO( 'mysql:dbname=' . $dbname . ';host=' . getenv ( 'DBHOST' ), getenv ( 'DBUSER' ), getenv ( 'DBPASSWORD' )); $this ->rdbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e ) { die ( "Failed to connect: " . $e ->getMessage()); } $sql = "SET TIME_ZONE = '" . $timezone . "'" ; $this ->executeSQL( $sql ); } function dropExampleTable() { $sql = "DROP TABLE IF EXISTS example" ; $this ->executeSQL( $sql ); } function endTransaction(){ $this ->rdbh->commit(); } function executeSQL( $sql ) { $sqls = explode ( ';' , $sql ); foreach ( $sqls as $sql ) { if ( $sql ) { $sth = $this ->rdbh->prepare( $sql ); $sth ->execute(); } } } function rollback(){ $this ->rdbh->rollback(); } function selectFromExampleTable(){ $sql = "SELECT id, name, sex, DATE_FORMAT(created_at, '%Y/%m/%d %H:%i:%S') AS created_at, DATE_FORMAT(updated_at, '%Y/%m/%d %H:%i:%S') AS updated_at FROM example" ; $sth = $this ->rdbh->prepare( $sql ); $sth ->execute(); while ( $row = $sth ->fetch(PDO::FETCH_ASSOC)){ print (implode( "\t" , [ $row [ 'id' ], $row [ 'name' ], $row [ 'sex' ], $row [ 'created_at' ], $row [ 'updated_at' ]]). "\n" ); } } } if ( !isset(debug_backtrace()[0]) ) { $rdb = new MysqlManipulator(); try { $rdb ->dbconnect(); } catch (PDOException $e ){ echo $e ->getMessage(); exit (1); } try { $rdb ->beginTransaction(); } catch (PDOException $e ){ echo $e ->getMessage(); $rdb ->dbclose(); exit (1); } try { $rdb ->dropExampleTable(); $rdb ->createExampleTable(); $rdb ->createExampleData(); $rdb ->selectFromExampleTable(); $rdb ->dropExampleTable(); $rdb ->endTransaction(); $rdb ->dbclose(); } catch (PDOException $e ){ $rdb ->rollback(); $rdb ->dbclose(); throw $e ; } } |
Python
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 | from firstclass_dotenv import Dotenv import mysql.connector import os class MysqlManipulator(): def __init__( self , opt = []): dotenv = Dotenv() dotenv.load() def beginTransaction( self ): sql = "BEGIN" self .executeSQL(sql) def createExampleTable( self ): sql = '''\ CREATE TABLE example ( id int NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, sex ENUM('male', 'female') DEFAULT NULL, created_at timestamp NOT NULL DEFAULT NOW(), updated_at timestamp NOT NULL DEFAULT NOW(), PRIMARY KEY (id), KEY `created_at_idx` (`created_at`), KEY `name_idx` (`name`), KEY `updated_at_idx` (`updated_at`) ); ''' self .executeSQL(sql) def createExampleData( self ): datas = [ [ 'name1' , 'male' ], [ 'name2' , 'female' ] ] for data in datas: self .cursor.execute( "INSERT INTO example (name, sex) VALUES (%s, %s)" , [data[ 0 ], data[ 1 ]]) def dbclose( self ): self .rdbh.close() def dbconnect( self ): dbname = "example" timezone = "Asia/Tokyo" self .rdbh = mysql.connector.connect(host = os.environ[ 'DBHOST' ], user = os.environ[ 'DBUSER' ], passwd = os.environ[ 'DBPASSWORD' ], database = dbname) self .cursor = self .rdbh.cursor() self .executeSQL( "SET TIME_ZONE = '" + timezone + "'" ) def dropExampleTable( self ): sql = "DROP TABLE IF EXISTS example" self .executeSQL(sql) def endTransaction( self ): self .rdbh.commit() def executeSQL( self , sql): self .cursor.execute(sql) def rollback( self ): self .rdbh.rollback() def selectFromExampleTable( self ): sql = "SELECT id, name, sex, DATE_FORMAT(created_at, '%Y/%m/%d %H:%i:%S') AS created_at, DATE_FORMAT(updated_at, '%Y/%m/%d %H:%i:%S') AS updated_at FROM example" self .cursor.execute(sql) for [ id , name, sex, created_at, updated_at] in self .cursor: print ( "\t" .join([ str ( id ), name, sex, str (created_at), str (updated_at)])) if __name__ = = "__main__" : rdb = MysqlManipulator() rdb.dbconnect() try : rdb.beginTransaction() try : rdb.dropExampleTable() rdb.createExampleTable() rdb.createExampleData() rdb.selectFromExampleTable() rdb.dropExampleTable() rdb.endTransaction() except Exception as e: rdb.rollback() print (e) finally : rdb.dbclose() except Exception as e: print (e) finally : rdb.dbclose() |
Ruby
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 | require 'dotenv' require 'mysql2' class MysqlManipulator def initialize(opt=[]) Dotenv.load( File .join( File .dirname( File .expand_path( __FILE__ )), '.env' ) ) end def dbconnect dbname = "example" timezone = "Asia/Tokyo" @rdbh = Mysql2::Client. new ( :host => ENV [ "DBHOST" ], :username => ENV [ "DBUSER" ], :password => ENV [ "DBPASSWORD" ], :database => dbname) sql = "SET TIME_ZONE = '" + timezone + "'" @rdbh .query(sql) end def beginTransaction sql = "BEGIN" executeSQL(sql) end def createExampleTable sql = '' ' CREATE TABLE example ( id int NOT NULL AUTO_INCREMENT , name VARCHAR ( 255 ) NOT NULL , sex ENUM ( "male" , "female" ) DEFAULT NULL , created_at timestamp NOT NULL DEFAULT NOW (), updated_at timestamp NOT NULL DEFAULT NOW (), PRIMARY KEY (id), KEY created_at_idx (created_at), KEY name_idx (name), KEY updated_at_idx (updated_at) ); '' ' executeSQL(sql) end def createExampleData datas = [ [ 'name1' , 'male' ], [ 'name2' , 'female' ] ] sth = @rdbh .prepare( "INSERT INTO example (name, sex) VALUES (?, ?)" ) datas. each do |data| sth.execute(data[ 0 ], data[ 1 ]) end end def dbclose @rdbh .close() end def dropExampleTable sql = "DROP TABLE IF EXISTS example" executeSQL(sql) end def endTransaction sql = "COMMIT" @rdbh .query(sql) end def executeSQL(sql) @rdbh .query(sql) end def selectFromExampleTable sql = "SELECT id, name, sex, DATE_FORMAT(created_at, '%Y/%m/%d %H:%i:%S') AS created_at, DATE_FORMAT(updated_at, '%Y/%m/%d %H:%i:%S') AS updated_at FROM example" results = @rdbh .query(sql) results. each do |row| print([row[ "id" ], row[ 'name' ], row[ 'sex' ], row[ 'created_at' ], row[ 'updated_at' ]].join( "\t" ) + "\n" ) end end end if $0 == __FILE__ rdb = MysqlManipulator. new () begin rdb.dbconnect() rescue => err print(err) exit( 1 ) end begin rdb.beginTransaction() begin rdb.dropExampleTable() rdb.createExampleTable() rdb.createExampleData() rdb.selectFromExampleTable() rdb.dropExampleTable() rdb.endTransaction() rescue => err puts( "Automatically roll backed." ) print(err) rdb.dbclose() exit( 1 ) end rescue => err rdb.dbclose() print(err) exit( 1 ) end end |
![](https://vpshikaku.com/app/uploads/vpshikaku.com/2nZbntr9-e1587540364868.jpg)
作者: 栗田 創。1st Class, inc的首席执行官/首席技术官。搜索引擎工程师、网站管理员
证书: AWS Solution Architect,Professional Scrum Master I ,一级IT工程师(日本国家证书),TOEIC900+,东京大学経済学部経営学科卒业
请关注我的Weblio帐户以获取更新
IT相关服务:VPS排名