MySQL.jl Documentation

DBInterface.connect(MySQL.Connection, host::String, user::String, passwd::String; db::String="", port::Integer=3306, unix_socket::String=API.MYSQL_DEFAULT_SOCKET, client_flag=API.CLIENT_MULTI_STATEMENTS, opts = Dict())

Connect to a MySQL database with provided host, user, and passwd positional arguments. Supported keyword arguments include:

• db::String="": attach to a database by default
• port::Integer=3306: connect to the database on a specific port
• unix_socket::String: specifies the socket or named pipe that should be used
• found_rows::Bool=false: Return the number of matched rows instead of number of changed rows
• no_schema::Bool=false: Forbids the use of database.tablename.column syntax and forces the SQL parser to generate an error.
• compress::Bool=false: Use compression protocol
• ignore_space::Bool=false: Allows spaces after function names. All function names will become reserved words.
• local_files::Bool=false: Allows LOAD DATA LOCAL statements
• multi_statements::Bool=false: Allows the client to send multiple statements in one command. Statements will be divided by a semicolon.
• multi_results::Bool=false: currently not supported by MySQL.jl
• init_command="": Command(s) which will be executed when connecting and reconnecting to the server.
• connect_timeout::Integer: Connect timeout in seconds
• reconnect::Bool: Enable or disable automatic reconnect.
• read_timeout::Integer: Specifies the timeout in seconds for reading packets from the server.
• write_timeout::Integer: Specifies the timeout in seconds for reading packets from the server.
• data_truncation::Bool: Enable or disable reporting data truncation errors for prepared statements
• charset_dir::String: character set files directory
• charset_name::String: Specify the default character set for the connection
• bind::String: Specify the network interface from which to connect to the database, like "192.168.8.3"
• max_allowed_packet::Integer: The maximum packet length to send to or receive from server. The default is 16MB, the maximum 1GB.
• net_buffer_length::Integer: The buffer size for TCP/IP and socket communication. Default is 16KB.
• named_pipe::Bool: For Windows operating systems only: Use named pipes for client/server communication.
• protocol::MySQL.API.mysql_protocol_type: Specify the type of client/server protocol. Possible values are: MySQL.API.MYSQL_PROTOCOL_TCP, MySQL.API.MYSQL_PROTOCOL_SOCKET, MySQL.API.MYSQL_PROTOCOL_PIPE, MySQL.API.MYSQL_PROTOCOL_MEMORY.
• ssl_key::String: Defines a path to a private key file to use for TLS. This option requires that you use the absolute path, not a relative path. If the key is protected with a passphrase, the passphrase needs to be specified with passphrase keyword argument.
• passphrase::String: Specify a passphrase for a passphrase-protected private key, as configured by the ssl_key keyword argument.
• ssl_cert::String: Defines a path to the X509 certificate file to use for TLS. This option requires that you use the absolute path, not a relative path.
• ssl_ca::String: Defines a path to a PEM file that should contain one or more X509 certificates for trusted Certificate Authorities (CAs) to use for TLS. This option requires that you use the absolute path, not a relative path.
• ssl_capath::String: Defines a path to a directory that contains one or more PEM files that should each contain one X509 certificate for a trusted Certificate Authority (CA) to use for TLS. This option requires that you use the absolute path, not a relative path. The directory specified by this option needs to be run through the openssl rehash command.
• ssl_cipher::String: Defines a list of permitted ciphers or cipher suites to use for TLS, like "DHE-RSA-AES256-SHA"
• ssl_crl::String: Defines a path to a PEM file that should contain one or more revoked X509 certificates to use for TLS. This option requires that you use the absolute path, not a relative path.
• ssl_crlpath::String: Defines a path to a directory that contains one or more PEM files that should each contain one revoked X509 certificate to use for TLS. This option requires that you use the absolute path, not a relative path. The directory specified by this option needs to be run through the openssl rehash command.
• ssl_verify_server_cert::Bool: Enables (or disables) server certificate verification.
• ssl_enforce::Bool: Whether to force TLS
• default_auth::String: Default authentication client-side plugin to use.
• connection_handler::String: Specify the name of a connection handler plugin.
• plugin_dir::String: Specify the location of client plugins. The plugin directory can also be specified with the MARIADBPLUGINDIR environment variable.
• secure_auth::Bool: Refuse to connect to the server if the server uses the mysqloldpassword authentication plugin. This mode is off by default, which is a difference in behavior compared to MySQL 5.6 and later, where it is on by default.
• server_public_key::String: Specifies the name of the file which contains the RSA public key of the database server. The format of this file must be in PEM format. This option is used by the cachingsha2password client authentication plugin.
• read_default_file::Bool: only the default option files are read
• option_file::String: the argument is interpreted as a path to a custom option file, and only that option file is read.
• read_default_group::Bool: only the default option groups are read from specified option file(s)
• option_group::String: it is interpreted as a custom option group, and that custom option group is read in addition to the default option groups.

DBInterface.close!(conn::MySQL.Connection)

Close a MySQL.Connection opened by DBInterface.connect.

DBInterface.close!(cursor)

Close a cursor. No more results will be available.

DBInterface.close!(stmt)

Close a prepared statement and free any underlying resources. The statement should not be used in any way afterwards.

DBInterface.close!(cursor)

Close a cursor. No more results will be available.

MySQL.escape(conn::MySQL.Connection, str::String) -> String

Escapes a string using mysql_real_escape_string(), returns the escaped string.

DBInterface.execute(conn::MySQL.Connection, sql) => MySQL.TextCursor

Execute the SQL sql statement with the database connection conn. Parameter binding is only supported via prepared statements, see ?DBInterface.prepare(conn, sql). Returns a Cursor object, which iterates resultset rows and satisfies the Tables.jl interface, meaning results can be sent to any valid sink function (DataFrame(cursor), CSV.write("results.csv", cursor), etc.). Specifying mysql_store_result=false will avoid buffering the full resultset to the client after executing the query, which has memory use advantages, though ties up the database server since resultset rows must be fetched one at a time.

DBInterface.execute(stmt, params; mysql_store_result=true) => MySQL.Cursor

Execute a prepared statement, optionally passing params to be bound as parameters (like ? in the sql). Returns a Cursor object, which iterates resultset rows and satisfies the Tables.jl interface, meaning results can be sent to any valid sink function (DataFrame(cursor), CSV.write("results.csv", cursor), etc.). Specifying mysql_store_result=false will avoid buffering the full resultset to the client after executing the query, which has memory use advantages, though ties up the database server since resultset rows must be fetched one at a time.

DBInterface.prepare(conn::MySQL.Connection, sql) => MySQL.Statement

Send a sql SQL string to the database to be prepared, returning a MySQL.Statement object that can be passed to DBInterface.execute(stmt, args...) to be repeatedly executed, optionally passing args for parameters to be bound on each execution.

Note that DBInterface.close!(stmt) should be called once statement executions are finished. Apart from freeing resources, it has been noted that too many unclosed statements and resultsets, used in conjunction with streaming queries (i.e. mysql_store_result=false) has led to occasional resultset corruption.

DBInterface.lastrowid(c::MySQL.TextCursor)

Return the last inserted row id.

DBInterface.lastrowid(c::MySQL.Cursor)

Return the last inserted row id.