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

Mysql

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] io

@return [[BasicSocket, OpenSSL::SSL::SSLSocket], 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] ssl_context_params

@return [Hash] See OpenSSL::SSL::Context#set_params

@!attribute [rw] get_server_public_key

@return [Boolean]

@!attribute [rw] connect_attrs

@return [Hash]

@!attribute [rw] yield_null_result

@return [Boolean]

@!attribute [rw] return_result

@return [Boolean]

@!attribute [rw] with_table

@return [Boolean]

@!attribute [rw] auto_store_result

@return [Boolean]

@!attribute [rw] cast

@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

SESSION_TRACK_GTIDS

SESSION_TRACK_SCHEMA

SESSION_TRACK_STATE_CHANGE

SESSION_TRACK_SYSTEM_VARIABLES

SESSION_TRACK_TRANSACTION_CHARACTERISTICS

SESSION_TRACK_TRANSACTION_STATE

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

fields [R]

@return [Array<Mysql::Field>] fields of result set

protocol [R]

@private

Public Class Methods

connect (*args, **opts)

Source

# File lib/mysql.rb, line 112
def connect(*args, **opts)
  self.new(*args, **opts).connect
end

Make Mysql object and connect to mysqld. parameter is same as arguments for {#initialize}. @return [Mysql]

default_options ()

Source

# File lib/mysql.rb, line 132
def default_options
  @default_options ||= DEFAULT_OPTS.dup
end

escape_string (str)

Source

# File lib/mysql.rb, line 119
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

Escape special character in string. @param [String] str @return [String]

Also aliased as: quote

new (*args, **opts)

Source

# File lib/mysql.rb, line 176
def initialize(*args, **opts)
  @fields = nil
  @result = nil
  @protocol = nil
  @sqlstate = "00000"
  @host_info = nil
  @last_error = nil
  @opts = Mysql.default_options.dup
  parse_args(args, opts)
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 :ssl_context_params [Hash<Symbol, String>]
@option opts :get_server_public_key [Boolean]
@option opts :connect_attrs [Hash]
@option opts :io [BasicSocket, OpenSSL::SSL::SSLSocket] Existing socket instance that will be used instead of creating a new socket

quote (str)

Alias for: escape_string

Public Instance Methods

affected_rows ()

Source

# File lib/mysql.rb, line 341
def affected_rows
  @protocol ? @protocol.affected_rows : 0
end

@return [Integer] number of affected records by insert/update/delete.

autocommit (flag)

Source

# File lib/mysql.rb, line 559
def autocommit(flag)
  query "set autocommit=#{flag ? 1 : 0}"
  self
end

Set autocommit mode @param [Boolean] flag @return [Mysql] self

character_set_name ()

Source

# File lib/mysql.rb, line 295
def character_set_name
  @protocol.charset.name
end

@return [String] charset name

charset ()

Source

# File lib/mysql.rb, line 279
def charset
  @opts[:charset]
end

@return [Mysql::Charset] character set of MySQL connection

charset= (cs)

Source

# File lib/mysql.rb, line 285
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
end

Set charset of MySQL connection. @param [String, Mysql::Charset] cs

close ()

Source

# File lib/mysql.rb, line 251
def close
  if @protocol
    @protocol.quit_command
    @protocol = nil
  end
  return self
end

Disconnect from mysql. @return [Mysql] self

close! ()

Source

# File lib/mysql.rb, line 261
def close!
  if @protocol
    @protocol.close
    @protocol = nil
  end
  return self
end

Disconnect from mysql without QUIT packet. @return [Mysql] self

commit ()

Source

# File lib/mysql.rb, line 544
def commit
  query 'commit'
  self
end

Commit transaction @return [Mysql] self

connect (*args, **opts)

Source

# File lib/mysql.rb, line 190
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

Connect to mysqld. parameter is same as arguments for {#initialize}. @return [Mysql] self

errno ()

Source

# File lib/mysql.rb, line 300
def errno
  @last_error ? @last_error.errno : 0
end

@return [Integer] last error number

error ()

Source

# File lib/mysql.rb, line 305
def error
  @last_error&.error
end

@return [String] last error message

escape_string (str)

Source

# File lib/mysql.rb, line 273
def escape_string(str)
  self.class.escape_string str
end

Escape special character in MySQL.

@param [String] str return [String]

Also aliased as: quote

field_count ()

Source

# File lib/mysql.rb, line 315
def field_count
  @fields.size
end

@return [Integer] number of columns for last query

host_info ()

Source

# File lib/mysql.rb, line 320
def host_info
  @host_info
end

@return [String] connection type

info ()

Source

# File lib/mysql.rb, line 336
def info
  @protocol&.message
end

@return [String] information for last query

insert_id ()

Source

# File lib/mysql.rb, line 346
def insert_id
  @protocol ? @protocol.insert_id : 0
end

@return [Integer] latest auto_increment value

kill (pid)

Source

# File lib/mysql.rb, line 358
def kill(pid)
  query "KILL #{pid}"
  self
end

Kill query. @param [Integer] pid thread id @return [Mysql] self

more_results? ()

Source

# File lib/mysql.rb, line 442
def more_results?
  @protocol.more_results?
end

@return [Boolean] true if multiple queries are specified and unexecuted queries exists.

next_result (**opts)

Source

# File lib/mysql.rb, line 450
def next_result(**opts)
  return nil unless more_results?
  opts = @opts.merge(opts)
  @protocol.get_result
  @result = @fields = nil
  return result(**opts) if opts[:return_result]
  true
end

execute next query if multiple queries are specified. @return [Mysql::Result] result set of query if return_result is true. @return [true] if return_result is false and result exists. @return [nil] query returns no results.

parse_args (args, opts)

Source

# File lib/mysql.rb, line 203
def parse_args(args, opts)
  unless args.empty?
    case args[0]
    when URI
      uri = args[0]
    when /\Amysql:\/\//
      uri = URI.parse(args[0])
    when String, nil
      @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
    end
  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

ping ()

Source

# File lib/mysql.rb, line 479
def ping
  check_connection
  @protocol.ping_command
  self
end

Check whether the connection is available. @return [Mysql] self

prepare (str, **opts)

Source

# File lib/mysql.rb, line 462
def prepare(str, **opts)
  opts = @opts.merge(opts)
  st = Stmt.new(@protocol, **opts)
  st.prepare str
  st
end

Parse prepared-statement. @param [String] str query string @return [Mysql::Stmt] Prepared-statement object

query (str, **opts, &block)

Source

# File lib/mysql.rb, line 373
def query(str, **opts, &block)
  opts = @opts.merge(opts)
  check_connection
  @fields = nil
  begin
    @result = nil
    @protocol.query_command str
    if block
      while true
        @result = nil
        @protocol.get_result
        res = result(**opts)
        block.call res if res || opts[:yield_null_result]
        break unless more_results?
      end
      return self
    end
    @protocol.get_result
    return self unless opts[:return_result]
    return result(**opts)
  rescue ServerError => e
    @last_error = e
    @sqlstate = e.sqlstate
    raise
  end
end

Execute query string. @param str [String] Query. @param return_result [Boolean] @param yield_null_result [Boolean] @return [Mysql::Result] if return_result is true and the query returns result set. @return [nil] if return_result is true and the query does not return result set. @return [self] if return_result is false or block is specified. @example

my.query("select 1,NULL,'abc'").fetch  # => [1, nil, "abc"]
my.query("select 1,NULL,'abc'"){|res| res.fetch}

quote (str)

Alias for: escape_string

refresh (op)

Source

# File lib/mysql.rb, line 488
def refresh(op)
  if server_version < 80400
    check_connection
    @protocol.refresh_command op
  else
    q = case op
        when REFRESH_GRANT
          "FLUSH PRIVILEGES"
        when REFRESH_LOG
          "FLUSH LOGS"
        when REFRESH_TABLES
          "FLUSH TABLES"
        when REFRESH_HOSTS
          "TRUNCATE TABLE performance_schema.host_cache"
        when REFRESH_STATUS
          "FLUSH STATUS"
        when REFRESH_SLAVE
          "RESET REPLICA"
        when REFRESH_MASTER
          "RESET BINARY LOGS AND GTIDS"
        else
          raise "unsupported operation for #{server_version}"
        end
    query q
  end
  self
end

Flush tables or caches. @param [Integer] op operation. Use Mysql::REFRESH_* value. @return [Mysql] self

reload ()

Source

# File lib/mysql.rb, line 518
def reload
  refresh Mysql::REFRESH_GRANT
end

Reload grant tables. @return [Mysql] self

result (**opts)

Source

# File lib/mysql.rb, line 403
def result(**opts)
  return nil if @protocol.field_count.nil? || @protocol.field_count == 0
  return @result if @result
  @fields = @protocol.retr_fields
  opts = @opts.merge(opts)
  @result = Result.new(@fields, @protocol, **opts)
end

return Mysql::Result for last query. @return [Mysql::Result] @return [nil] if no results

rollback ()

Source

# File lib/mysql.rb, line 551
def rollback
  query 'rollback'
  self
end

Rollback transaction @return [Mysql] self

select_db (db)

Source

# File lib/mysql.rb, line 524
def select_db(db)
  query "use #{db}"
  self
end

Select default database @return [Mysql] self

server_info ()

Source

# File lib/mysql.rb, line 325
def server_info
  check_connection
  @protocol.server_info
end

@return [String] server version

server_version ()

Source

# File lib/mysql.rb, line 331
def server_version
  @protocol&.server_version
end

@return [Integer] server version

session_track ()

Source

# File lib/mysql.rb, line 566
def session_track
  @protocol.session_track
end

session track @return [Hash]

set_server_option (opt)

Source

# File lib/mysql.rb, line 435
def set_server_option(opt)
  check_connection
  @protocol.set_option_command opt
  self
end

Set server option. @param [Integer] opt {Mysql::OPTION_MULTI_STATEMENTS_ON} or {Mysql::OPTION_MULTI_STATEMENTS_OFF} @return [Mysql] self

shutdown (level=0)

Source

# File lib/mysql.rb, line 531
def shutdown(level=0)
  check_connection
  @protocol.shutdown_command level
  self
end

shutdown server. @return [Mysql] self

sqlstate ()

Source

# File lib/mysql.rb, line 310
def sqlstate
  @last_error ? @last_error.sqlstate : "00000"
end

@return [String] sqlstate for last error

stat ()

Source

# File lib/mysql.rb, line 538
def stat
  @protocol ? @protocol.statistics_command : 'MySQL server has gone away'
end

@return [String] statistics message

stmt (**opts)

Source

# File lib/mysql.rb, line 472
def stmt(**opts)
  opts = @opts.merge(opts)
  Stmt.new(@protocol, **opts)
end

@private Make empty prepared-statement object. @return [Mysql::Stmt] If block is not specified.

store_result (**opts)

Source

# File lib/mysql.rb, line 414
def store_result(**opts)
  result(auto_store_result: true, **opts.merge)
end

return Mysql::Result for last query with all data. @return [Mysql::Result] @return [nil] if no results

thread_id ()

Source

# File lib/mysql.rb, line 427
def thread_id
  check_connection
  @protocol.thread_id
end

@return [Integer] Thread ID

use_result (**opts)

Source

# File lib/mysql.rb, line 422
def use_result(**opts)
  result(auto_store_result: false, **opts.merge)
end

return Mysql::Result for last query without data. Mysql::Result#data_seek, row_tell, row_seek cannot be used. @return [Mysql::Result] @return [nil] if no results

warning_count ()

Source

# File lib/mysql.rb, line 351
def warning_count
  @protocol ? @protocol.warning_count : 0
end

@return [Integer] number of warnings for previous query