class Mysql
MySQL connection class. @example
my = Mysql.connect('hostname', 'user', 'password', 'dbname') res = my.query 'select col1,col2 from tbl where id=123' res.each do |c1, c2| p c1, c2 end
Copyright © 2003 TOMITA Masahiro tommy@tmtm.org
Copyright © 2003-2010 TOMITA Masahiro tommy@tmtm.org
Constants
- CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS
- CLIENT_CAPABILITY_EXTENSION
- CLIENT_COMPRESS
- CLIENT_CONNECT_ATTRS
- CLIENT_CONNECT_WITH_DB
- CLIENT_DEPRECATE_EOF
- CLIENT_FOUND_ROWS
- CLIENT_IGNORE_SIGPIPE
- CLIENT_IGNORE_SPACE
- CLIENT_INTERACTIVE
- CLIENT_LOCAL_FILES
- CLIENT_LONG_FLAG
- CLIENT_LONG_PASSWORD
Client flag
- CLIENT_MULTI_RESULTS
- CLIENT_MULTI_STATEMENTS
- CLIENT_NO_SCHEMA
- CLIENT_ODBC
- CLIENT_OPTIONAL_RESULTSET_METADATA
- CLIENT_PLUGIN_AUTH
- CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA
- CLIENT_PROTOCOL_41
- CLIENT_PS_MULTI_RESULTS
- CLIENT_REMEMBER_OPTIONS
- CLIENT_RESERVED
- CLIENT_SECURE_CONNECTION
- CLIENT_SESSION_TRACK
- CLIENT_SSL
- CLIENT_SSL_VERIFY_SERVER_CERT
- CLIENT_TRANSACTIONS
- CLIENT_ZSTD_COMPRESSION_ALGORITHM
- COM_BINLOG_DUMP
- COM_BINLOG_DUMP_GTID
- COM_CHANGE_USER
- COM_CLONE
- COM_CONNECT
- COM_CONNECT_OUT
- COM_CREATE_DB
- COM_DAEMON
- COM_DEBUG
- COM_DELAYED_INSERT
- COM_DROP_DB
- COM_FIELD_LIST
- COM_INIT_DB
- COM_PING
- COM_PROCESS_INFO
- COM_PROCESS_KILL
- COM_QUERY
- COM_QUIT
- COM_REFRESH
- COM_REGISTER_SLAVE
- COM_RESET_CONNECTION
- COM_SET_OPTION
- COM_SHUTDOWN
- COM_SLEEP
Command
- COM_STATISTICS
- COM_STMT_CLOSE
- COM_STMT_EXECUTE
- COM_STMT_FETCH
- COM_STMT_PREPARE
- COM_STMT_RESET
- COM_STMT_SEND_LONG_DATA
- COM_TABLE_DUMP
- COM_TIME
- DEFAULT_AUTH
- DEFAULT_OPTS
@!attribute [rw] host
@return [String, nil]
@!attribute [rw] username
@return [String, nil]
@!attribute [rw] password
@return [String, nil]
@!attribute [rw] database
@return [String, nil]
@!attribute [rw] port
@return [Integer, String, nil]
@!attribute [rw] socket
@return [String, nil] socket filename
@!attribute [rw] flags
@return [Integer, nil]
@!attribute [rw] connect_timeout
@return [Numeric, nil]
@!attribute [rw] read_timeout
@return [Numeric, nil]
@!attribute [rw] write_timeout
@return [Numeric, nil]
@!attribute [rw] init_command
@return [String, nil]
@!attribute [rw] local_infile
@return [Boolean]
@!attribute [rw] load_data_local_dir
@return [String, nil]
@!attribute [rw] ssl_mode
@return [String, Integer] 1 or "disabled" / 2 or "preferred" / 3 or "required"
@!attribute [rw] get_server_public_key
@return [Boolean]
- ENABLE_CLEARTEXT_PLUGIN
- INIT_COMMAND
- MYSQL_TCP_PORT
- MYSQL_UNIX_PORT
- OPTION_MULTI_STATEMENTS_OFF
- OPTION_MULTI_STATEMENTS_ON
Server Option
- OPT_BIND
- OPT_CAN_HANDLE_EXPIRED_PASSWORDS
- OPT_COMPRESS
- OPT_COMPRESSION_ALGORITHMS
- OPT_CONNECT_ATTR_ADD
- OPT_CONNECT_ATTR_DELETE
- OPT_CONNECT_ATTR_RESET
- OPT_CONNECT_TIMEOUT
Connection Option
- OPT_GET_SERVER_PUBLIC_KEY
- OPT_LOAD_DATA_LOCAL_DIR
- OPT_LOCAL_INFILE
- OPT_MAX_ALLOWED_PACKET
- OPT_NAMED_PIPE
- OPT_NET_BUFFER_LENGTH
- OPT_OPTIONAL_RESULTSET_METADATA
- OPT_PROTOCOL
- OPT_READ_TIMEOUT
- OPT_RECONNECT
- OPT_RETRY_COUNT
- OPT_SSL_CA
- OPT_SSL_CAPATH
- OPT_SSL_CERT
- OPT_SSL_CIPHER
- OPT_SSL_CRL
- OPT_SSL_CRLPATH
- OPT_SSL_FIPS_MODE
- OPT_SSL_KEY
- OPT_SSL_MODE
- OPT_TLS_CIPHERSUITES
- OPT_TLS_VERSION
- OPT_USE_RESULT
- OPT_WRITE_TIMEOUT
- OPT_ZSTD_COMPRESSION_LEVEL
- PLUGIN_DIR
- READ_DEFAULT_FILE
- READ_DEFAULT_GROUP
- REFRESH_BINARY_LOG
- REFRESH_DES_KEY_FILE
- REFRESH_ENGINE_LOG
- REFRESH_ERROR_LOG
- REFRESH_FAST
- REFRESH_FOR_EXPORT
- REFRESH_GENERAL_LOG
- REFRESH_GRANT
Refresh parameter
- REFRESH_HOSTS
- REFRESH_LOG
- REFRESH_MASTER
- REFRESH_OPTIMIZER_COSTS
- REFRESH_PERSIST
- REFRESH_QUERY_CACHE
- REFRESH_QUERY_CACHE_FREE
- REFRESH_READ_LOCK
- REFRESH_RELAY_LOG
- REFRESH_SLAVE
- REFRESH_SLOW_LOG
- REFRESH_STATUS
- REFRESH_TABLES
- REFRESH_THREADS
- REFRESH_USER_RESOURCES
- REPORT_DATA_TRUNCATION
- SERVER_MORE_RESULTS_EXISTS
- SERVER_PS_OUT_PARAMS
- SERVER_PUBLIC_KEY
- SERVER_QUERY_NO_GOOD_INDEX_USED
- SERVER_QUERY_NO_INDEX_USED
- SERVER_QUERY_WAS_SLOW
- SERVER_SESSION_STATE_CHANGED
- SERVER_STATUS_AUTOCOMMIT
- SERVER_STATUS_CURSOR_EXISTS
- SERVER_STATUS_DB_DROPPED
- SERVER_STATUS_IN_TRANS
Server Status
- SERVER_STATUS_IN_TRANS_READONLY
- SERVER_STATUS_LAST_ROW_SENT
- SERVER_STATUS_METADATA_CHANGED
- SERVER_STATUS_NO_BACKSLASH_ESCAPES
- SET_CHARSET_DIR
- SET_CHARSET_NAME
- SHARED_MEMORY_BASE_NAME
- SSL_MODE_DISABLED
SSL Mode
- SSL_MODE_PREFERRED
- SSL_MODE_REQUIRED
- SSL_MODE_VERIFY_CA
- SSL_MODE_VERIFY_IDENTITY
- VERSION
Attributes
@private
Public Class Methods
Make Mysql
object and connect to mysqld. parameter is same as arguments for {#initialize}. @return [Mysql]
# File lib/mysql.rb, line 82 def connect(*args, **opts) self.new(*args, **opts).connect end
Escape special character in string. @param [String] str @return [String]
# File lib/mysql.rb, line 89 def escape_string(str) str.gsub(/[\0\n\r\\\'\"\x1a]/) do |s| case s when "\0" then "\\0" when "\n" then "\\n" when "\r" then "\\r" when "\x1a" then "\\Z" else "\\#{s}" end end end
@overload initialize(uri, **opts)
@param uri [String, URI] "mysql://username:password@host:port/database?param=value&..." / "mysql://username:password@%2Ftmp%2Fmysql.sock/database" / "mysql://username:password@/database?socket=/tmp/mysql.sock" @param opts [Hash] options
@overload initialize(host, username, password, database, port, socket, flags, **opts)
@param host [String] hostname mysqld running @param username [String] username to connect to mysqld @param password [String] password to connect to mysqld @param database [String] initial database name @param port [String] port number (used if host is not 'localhost' or nil) @param socket [String] socket filename (used if host is 'localhost' or nil) @param flags [Integer] connection flag. Mysql::CLIENT_* ORed @param opts [Hash] options
@overload initialize(host: nil, username: nil, password: nil, database: nil, port: nil, socket: nil, flags: nil, **opts)
@param host [String] hostname mysqld running @param username [String] username to connect to mysqld @param password [String] password to connect to mysqld @param database [String] initial database name @param port [String] port number (used if host is not 'localhost' or nil) @param socket [String] socket filename (used if host is 'localhost' or nil) @param flags [Integer] connection flag. Mysql::CLIENT_* ORed @param opts [Hash] options @option opts :host [String] hostname mysqld running @option opts :username [String] username to connect to mysqld @option opts :password [String] password to connect to mysqld @option opts :database [String] initial database name @option opts :port [String] port number (used if host is not 'localhost' or nil) @option opts :socket [String] socket filename (used if host is 'localhost' or nil) @option opts :flags [Integer] connection flag. Mysql::CLIENT_* ORed @option opts :charset [Mysql::Charset, String] character set @option opts :connect_timeout [Numeric, nil] @option opts :read_timeout [Numeric, nil] @option opts :write_timeout [Numeric, nil] @option opts :local_infile [Boolean] @option opts :load_data_local_dir [String] @option opts :ssl_mode [Integer] @option opts :get_server_public_key [Boolean]
# File lib/mysql.rb, line 139 def initialize(*args, **opts) @fields = nil @protocol = nil @sqlstate = "00000" @host_info = nil @last_error = nil @result_exist = false @opts = DEFAULT_OPTS.dup parse_args(args, opts) end
Public Instance Methods
@return [Integer] number of affected records by insert/update/delete.
# File lib/mysql.rb, line 306 def affected_rows @protocol ? @protocol.affected_rows : 0 end
Set autocommit mode @param [Boolean] flag @return [Mysql] self
# File lib/mysql.rb, line 486 def autocommit(flag) query "set autocommit=#{flag ? 1 : 0}" self end
@return [String] charset name
# File lib/mysql.rb, line 259 def character_set_name @protocol.charset.name end
@return [Mysql::Charset] character set of MySQL connection
# File lib/mysql.rb, line 242 def charset @opts[:charset] end
Set charset of MySQL connection. @param [String, Mysql::Charset
] cs
# File lib/mysql.rb, line 248 def charset=(cs) charset = cs.is_a?(Charset) ? cs : Charset.by_name(cs) if @protocol @protocol.charset = charset query "SET NAMES #{charset.name}" end @opts[:charset] = charset cs end
Disconnect from mysql. @return [Mysql] self
# File lib/mysql.rb, line 214 def close if @protocol @protocol.quit_command @protocol = nil end return self end
Disconnect from mysql without QUIT packet. @return [Mysql] self
# File lib/mysql.rb, line 224 def close! if @protocol @protocol.close @protocol = nil end return self end
Commit transaction @return [Mysql] self
# File lib/mysql.rb, line 471 def commit query 'commit' self end
Connect to mysqld. parameter is same as arguments for {#initialize}. @return [Mysql] self
# File lib/mysql.rb, line 153 def connect(*args, **opts) parse_args(args, opts) if @opts[:flags] & CLIENT_COMPRESS != 0 warn 'unsupported flag: CLIENT_COMPRESS' if $VERBOSE @opts[:flags] &= ~CLIENT_COMPRESS end @protocol = Protocol.new(@opts) @protocol.authenticate @host_info = (@opts[:host].nil? || @opts[:host] == "localhost") ? 'Localhost via UNIX socket' : "#{@opts[:host]} via TCP/IP" query @opts[:init_command] if @opts[:init_command] return self end
@return [Integer] last error number
# File lib/mysql.rb, line 264 def errno @last_error ? @last_error.errno : 0 end
@return [String] last error message
# File lib/mysql.rb, line 269 def error @last_error && @last_error.error end
Escape special character in MySQL.
@param [String] str return [String]
# File lib/mysql.rb, line 236 def escape_string(str) self.class.escape_string str end
@return [Integer] number of columns for last query
# File lib/mysql.rb, line 279 def field_count @fields.size end
@return [String] connection type
# File lib/mysql.rb, line 284 def host_info @host_info end
@return [String] information for last query
# File lib/mysql.rb, line 301 def info @protocol && @protocol.message end
@return [Integer] latest auto_increment value
# File lib/mysql.rb, line 311 def insert_id @protocol ? @protocol.insert_id : 0 end
Kill query. @param [Integer] pid thread id @return [Mysql] self
# File lib/mysql.rb, line 323 def kill(pid) check_connection @protocol.kill_command pid self end
@return [Boolean] true if multiple queries are specified and unexecuted queries exists.
# File lib/mysql.rb, line 391 def more_results @protocol.server_status & SERVER_MORE_RESULTS_EXISTS != 0 end
execute next query if multiple queries are specified. @return [Boolean] true if next query exists.
# File lib/mysql.rb, line 398 def next_result return false unless more_results check_connection @fields = nil nfields = @protocol.get_result if nfields @fields = @protocol.retr_fields nfields @result_exist = true end return true end
# File lib/mysql.rb, line 166 def parse_args(args, opts) case args[0] when URI uri = args[0] when /\Amysql:\/\// uri = URI.parse(args[0]) when String @opts[:host], user, passwd, dbname, port, socket, flags = *args @opts[:username] = user if user @opts[:password] = passwd if passwd @opts[:database] = dbname if dbname @opts[:port] = port if port @opts[:socket] = socket if socket @opts[:flags] = flags if flags when Hash # skip when nil # skip end if uri host = uri.hostname.to_s host = URI.decode_www_form_component(host) if host.start_with?('/') @opts[:socket] = host host = '' end @opts[:host] = host @opts[:username] = URI.decode_www_form_component(uri.user.to_s) @opts[:password] = URI.decode_www_form_component(uri.password.to_s) @opts[:database] = uri.path.sub(/\A\/+/, '') @opts[:port] = uri.port opts = URI.decode_www_form(uri.query).to_h.transform_keys(&:intern).merge(opts) if uri.query opts[:flags] = opts[:flags].to_i if opts[:flags] end if args.last.kind_of? Hash opts = opts.merge(args.last) end @opts.update(opts) end
Check whether the connection is available. @return [Mysql] self
# File lib/mysql.rb, line 428 def ping check_connection @protocol.ping_command self end
Parse prepared-statement. @param [String] str query string @return [Mysql::Stmt] Prepared-statement object
# File lib/mysql.rb, line 413 def prepare(str) st = Stmt.new @protocol st.prepare str st end
Execute query string. @overload query(str)
@param [String] str Query. @return [Mysql::Result] @return [nil] if the query does not return result set.
@overload query(str, &block)
@param [String] str Query. @yield [Mysql::Result] evaluated per query. @return [self]
@example
my.query("select 1,NULL,'abc'").fetch # => [1, nil, "abc"] my.query("select 1,NULL,'abc'"){|res| res.fetch}
# File lib/mysql.rb, line 341 def query(str, &block) check_connection @fields = nil begin nfields = @protocol.query_command str if nfields @fields = @protocol.retr_fields nfields @result_exist = true end if block while true block.call store_result if @fields break unless next_result end return self end return @fields ? store_result : nil rescue ServerError => e @last_error = e @sqlstate = e.sqlstate raise end end
Flush tables or caches. @param [Integer] op operation. Use Mysql::REFRESH_* value. @return [Mysql] self
# File lib/mysql.rb, line 437 def refresh(op) check_connection @protocol.refresh_command op self end
Reload grant tables. @return [Mysql] self
# File lib/mysql.rb, line 445 def reload refresh Mysql::REFRESH_GRANT end
Rollback transaction @return [Mysql] self
# File lib/mysql.rb, line 478 def rollback query 'rollback' self end
Select default database @return [Mysql] self
# File lib/mysql.rb, line 451 def select_db(db) query "use #{db}" self end
@return [String] server version
# File lib/mysql.rb, line 289 def server_info check_connection @protocol.server_info end
@return [Integer] server version
# File lib/mysql.rb, line 295 def server_version check_connection @protocol.server_version end
Set server option. @param [Integer] opt {Mysql::OPTION_MULTI_STATEMENTS_ON} or {Mysql::OPTION_MULTI_STATEMENTS_OFF} @return [Mysql] self
# File lib/mysql.rb, line 384 def set_server_option(opt) check_connection @protocol.set_option_command opt self end
shutdown server. @return [Mysql] self
# File lib/mysql.rb, line 458 def shutdown(level=0) check_connection @protocol.shutdown_command level self end
@return [String] sqlstate for last error
# File lib/mysql.rb, line 274 def sqlstate @last_error ? @last_error.sqlstate : "00000" end
@return [String] statistics message
# File lib/mysql.rb, line 465 def stat @protocol ? @protocol.statistics_command : 'MySQL server has gone away' end
@private Make empty prepared-statement object. @return [Mysql::Stmt] If block is not specified.
# File lib/mysql.rb, line 422 def stmt Stmt.new @protocol end
Get all data for last query. @return [Mysql::Result]
# File lib/mysql.rb, line 367 def store_result check_connection raise ClientError, 'invalid usage' unless @result_exist res = Result.new @fields, @protocol @result_exist = false res end
@return [Integer] Thread ID
# File lib/mysql.rb, line 376 def thread_id check_connection @protocol.thread_id end
@return [Integer] number of warnings for previous query
# File lib/mysql.rb, line 316 def warning_count @protocol ? @protocol.warning_count : 0 end
Private Instance Methods
# File lib/mysql.rb, line 493 def check_connection raise ClientError::ServerGoneError, 'MySQL server has gone away' unless @protocol end