Chapter 28 Connectors and APIs


MySQL Connectors provide connectivity to the MySQL server for client programs. APIs provide low-level access to the MySQL protocol and MySQL resources. Both Connectors and the APIs enable you to connect and execute MySQL statements from another language or environment, including ODBC, Java (JDBC), Perl, Python, PHP, Ruby, and native C MySQL instances.

MySQL Connectors

Oracle develops a number of connectors:

  • Connector/C is a standalone replacement for the MySQL Client Library ( libmysqlclient ), to be used for C applications.

  • Connector/C++ enables C++ applications to connect to MySQL.

  • Connector/J provides driver support for connecting to MySQL from Java applications using the standard Java Database Connectivity (JDBC) API.

  • Connector/NET enables developers to create .NET applications that connect to MySQL. Connector/NET implements a fully functional ADO.NET interface and provides support for use with ADO.NET aware tools. Applications that use Connector/NET can be written in any supported .NET language.

    MySQL for Visual Studio works with Connector/NET and Microsoft Visual Studio 2012, 2013, 2015, and 2017. MySQL for Visual Studio provides access to MySQL objects and data from Visual Studio. As a Visual Studio package, it integrates directly into Server Explorer providing the ability to create new connections and work with MySQL database objects.

  • Connector/ODBC provides driver support for connecting to MySQL using the Open Database Connectivity (ODBC) API. Support is available for ODBC connectivity from Windows, Unix, and OS X platforms.

  • Connector/Python provides driver support for connecting to MySQL from Python applications using an API that is compliant with the Python DB API version 2.0 . No additional Python modules or MySQL client libraries are required.

The MySQL C API

For direct access to using MySQL natively within a C application, the C API provides low-level access to the MySQL client/server protocol through the libmysqlclient client library. This is the primary method used to connect to an instance of the MySQL server, and is used both by MySQL command-line clients and many of the MySQL Connectors and third-party APIs detailed here.

libmysqlclient is included in MySQL distributions and in Connector/C distributions.

另请参阅 Section 28.7.1, “MySQL C API Implementations” .

To access MySQL from a C application, or to build an interface to MySQL for a language not supported by the Connectors or APIs in this chapter, the C API is where to start. A number of programmer's utilities are available to help with the process; see Section 4.7, “MySQL Program Development Utilities” .

Third-Party MySQL APIs

The remaining APIs described in this chapter provide an interface to MySQL from specific application languages. These third-party solutions are not developed or supported by Oracle. Basic information on their usage and abilities is provided here for reference purposes only.

All the third-party language APIs are developed using one of two methods, using libmysqlclient or by implementing a native driver . The two solutions offer different benefits:

  • Using libmysqlclient offers complete compatibility with MySQL because it uses the same libraries as the MySQL client applications. However, the feature set is limited to the implementation and interfaces exposed through libmysqlclient and the performance may be lower as data is copied between the native language, and the MySQL API components.

  • Native drivers are an implementation of the MySQL network protocol entirely within the host language or environment. Native drivers are fast, as there is less copying of data between components, and they can offer advanced functionality not available through the standard MySQL API. Native drivers are also easier for end users to build and deploy because no copy of the MySQL client libraries is needed to build the native driver components.

Table 28.1, “MySQL APIs and Interfaces” lists many of the libraries and interfaces available for MySQL.

Table 28.1 MySQL APIs and Interfaces

Environment API Type Notes
Ada GNU Ada MySQL Bindings libmysqlclient See MySQL Bindings for GNU Ada
C C API libmysqlclient See Section 28.7, “MySQL C API” .
C Connector/C Replacement for libmysqlclient See MySQL Connector/C Developer Guide .
C++ Connector/C++ libmysqlclient See MySQL Connector/C++ 8.0 Developer Guide .
MySQL++ libmysqlclient See MySQL++ website .
MySQL wrapped libmysqlclient See MySQL wrapped .
Cocoa MySQL-Cocoa libmysqlclient Compatible with the Objective-C Cocoa environment. See http://mysql-cocoa.sourceforge.net/
D MySQL for D libmysqlclient See MySQL for D .
Eiffel Eiffel MySQL libmysqlclient See Section 28.13, “MySQL Eiffel Wrapper” .
Erlang erlang-mysql-driver libmysqlclient See erlang-mysql-driver .
Haskell Haskell MySQL Bindings Native Driver See Brian O'Sullivan's pure Haskell MySQL bindings .
hsql-mysql libmysqlclient See MySQL driver for Haskell .
Java/JDBC Connector/J Native Driver See MySQL Connector/J 5.1 Developer Guide .
Kaya MyDB libmysqlclient See MyDB .
Lua LuaSQL libmysqlclient See LuaSQL .
.NET/Mono Connector/NET Native Driver See MySQL Connector/NET Developer Guide .
Objective Caml OBjective Caml MySQL Bindings libmysqlclient See MySQL Bindings for Objective Caml .
Octave Database bindings for GNU Octave libmysqlclient See Database bindings for GNU Octave .
ODBC Connector/ODBC libmysqlclient See MySQL Connector/ODBC Developer Guide .
Perl DBI / DBD::mysql libmysqlclient See Section 28.9, “MySQL Perl API” .
Net::MySQL Native Driver See Net::MySQL at CPAN
PHP mysql , ext/mysql interface (deprecated) libmysqlclient See Original MySQL API .
mysqli , ext/mysqli interface libmysqlclient See MySQL Improved Extension .
PDO_MYSQL libmysqlclient See MySQL Functions (PDO_MYSQL) .
PDO mysqlnd Native Driver
Python Connector/Python Native Driver See MySQL Connector/Python Developer Guide .
Python Connector/Python C Extension libmysqlclient See MySQL Connector/Python Developer Guide .
MySQLdb libmysqlclient See Section 28.10, “MySQL Python API” .
Ruby MySQL/Ruby libmysqlclient Uses libmysqlclient . See Section 28.11.1, “The MySQL/Ruby API” .
Ruby/MySQL Native Driver See Section 28.11.2, “The Ruby/MySQL API” .
Scheme Myscsh libmysqlclient See Myscsh .
SPL sql_mysql libmysqlclient See sql_mysql for SPL .
Tcl MySQLtcl libmysqlclient See Section 28.12, “MySQL Tcl API” .

28.1 MySQL Connector/C

The MySQL Connector/C manual is published in standalone form, not as part of the MySQL Reference Manual. For information, see these documents:

28.2 MySQL Connector/C++

The MySQL Connector/C++ manual is published in standalone form, not as part of the MySQL Reference Manual. For information, see these documents:

28.3 MySQL Connector/J

The MySQL Connector/J manual is published in standalone form, not as part of the MySQL Reference Manual. For information, see these documents:

28.4 MySQL Connector/NET

The MySQL Connector/NET manual is published in standalone form, not as part of the MySQL Reference Manual. For information, see these documents:

28.5 MySQL Connector/ODBC

The MySQL Connector/ODBC manual is published in standalone form, not as part of the MySQL Reference Manual. For information, see these documents:

28.6 MySQL Connector/Python

The MySQL Connector/Python manual is published in standalone form, not as part of the MySQL Reference Manual. For information, see these documents:

28.7 MySQL C API

The C API provides low-level access to the MySQL client/server protocol and enables C programs to access database contents. The C API code is distributed with MySQL and implemented in the libmysqlclient library. See Section 28.7.1, “MySQL C API Implementations” .

Most other client APIs use the libmysqlclient library to communicate with the MySQL server. (Exceptions are Connector/J and Connector/NET.) This means that, for example, you can take advantage of many of the same environment variables that are used by other client programs because they are referenced from the library. For a list of these variables, see Section 4.1, “Overview of MySQL Programs” .

For instructions on building client programs using the C API, see Section 28.7.4.1, “Building C API Client Programs” . For programming with threads, see Section 28.7.4.3, “Writing C API Threaded Client Programs” .

Note

If, after an upgrade, you experience problems with compiled client programs, such as Commands out of sync or unexpected core dumps, the programs were probably compiled using old header or library files. In this case, check the date of the mysql.h file and libmysqlclient.a library used for compilation to verify that they are from the new MySQL distribution. If not, recompile the programs with the new headers and libraries. Recompilation might also be necessary for programs compiled against the shared client library if the library major version number has changed (for example, from libmysqlclient.so.17 to libmysqlclient.so.18 ). For additional compatibility information, see Section 28.7.4.4, “Running C API Client Programs” .

Clients have a maximum communication buffer size. The size of the buffer that is allocated initially (16KB) is automatically increased up to the maximum size (16MB by default). Because buffer sizes are increased only as demand warrants, simply increasing the maximum limit does not in itself cause more resources to be used. This size check is mostly a precaution against erroneous statements and communication packets.

The communication buffer must be large enough to contain a single SQL statement (for client-to-server traffic) and one row of returned data (for server-to-client traffic). Each session's communication buffer is dynamically enlarged to handle any query or row up to the maximum limit. For example, if you have BLOB values that contain up to 16MB of data, you must have a communication buffer limit of at least 16MB (in both server and client). The default maximum built into the client library is 1GB, but the default maximum in the server is 1MB. You can increase this by changing the value of the max_allowed_packet parameter at server startup. See Section 5.1.1, “Configuring the Server” .

The MySQL server shrinks each communication buffer to net_buffer_length bytes after each query. For clients, the size of the buffer associated with a connection is not decreased until the connection is closed, at which time client memory is reclaimed.

28.7.1 MySQL C API Implementations

The MySQL C API is a C-based API that client applications written in C can use to communicate with MySQL Server. Client programs refer to C API header files at compile time and link to a C API library file, libmysqlclient , at link time.

There are two ways to obtain the C API header and library files required to build C API client programs:

  • Install a MySQL Server distribution.

  • Install a Connector/C distribution.

For both MySQL Server and Connector/C, you can install a binary distribution that contains the C API files pre-built, or you can use a source distribution and build the C API files yourself.

Normally, you install either a MySQL Server distribution or a Connector/C distribution, but not both. For information about issues involved with simultaneous MySQL Server and Connector/C installations, see Section 28.7.2, “Simultaneous MySQL Server and Connector/C Installations” .

The names of the library files to use when linking C API client applications depend on the library type and platform for which a distribution is built:

  • On Unix (and Unix-like) systems, the static library is libmysqlclient.a . The dynamic library is libmysqlclient.so on most Unix systems and libmysqlclient.dylib on OS X.

  • On Windows, the static library is mysqlclient.lib and the dynamic library is libmysql.dll . Windows distributions also include libmysql.lib , a static import library needed for using the dynamic library.

    Windows distributions also include a set of debug libraries. These have the same names as the nondebug libraries, but are located in the lib/debug library. You must use the debug libraries when compiling clients built using the debug C runtime.

On Unix, you may also see libraries that include _r in the names. Before MySQL 5.5, these were built as thread-safe (re-entrant) libraries separately from the non- _r libraries. As of 5.5, both libraries are the same and the _r names are symbolic links to the corresponding non- _r names. There is no need to use the _r libraries. For example, if you use mysql_config to obtain linker flags, you can use mysql_config --libs in all cases, even for threaded clients. There is no need to use mysql_config --libs_r .

28.7.2 Simultaneous MySQL Server and Connector/C Installations

MySQL Server and Connector/C installation packages both provide the files needed to build and run MySQL C API client programs. This section discusses when it is possible to install both products on the same system. For some packaging formats, this is possible without conflict. For others, both products cannot be installed at the same time.

This discussion assumes the use of similar package types for both products (for example, RPM packages for both products). It does not try to describe coexistence between packaging types (for example, use of RPM packages for one product and a tar file package for the other). Nor does it describe coexistence of packages provided by Oracle and those provided by third-party vendors.

If you install both products, it may be necessary to adjust your development tools or runtime environment to choose one set of header files and libraries over the other. See Section 28.7.4.1, “Building C API Client Programs” , and Section 28.7.4.4, “Running C API Client Programs” .

tar and Zip file packages install under the directory into which you unpack them. For example, you can unpack MySQL Server and Connector/C tar packages under /usr/local and they will unpack into distinct directory names without conflict.

Windows MSI installers use their own installation directory, so MySQL Server and Connector/C installers do not conflict.

OS X DMG packages install under the same parent directory but in a different subdirectory, so there is no conflict. For example:

/usr/local/mysql-5.6.11-osx10.7-x86_64/
/usr/local/mysql-connector-c-6.1.0-osx10.7-x86/
                        

Solaris PKG packages install under the same parent directory but in a different subdirectory, so there is no conflict. For example:

/opt/mysql/mysql
/opt/mysql/connector-c
                        

The Solaris Connector/C installer does not create any symlinks from system directories such as /usr/bin or /usr/lib into the installation directory. That must be done manually if desired after installation.

For RPM installations, there are several types of RPM packages. MySQL Server shared and devel RPM packages are similar to the corresponding Connector/C RPM packages. These RPM package types cannot coexist because the MySQL Server and Connector/C RPM packages use the same installation locations for the client library-related files. This means the following conditions hold:

  • If MySQL Server shared and devel RPM packages are installed, they provide the C API headers and libraries, and there is no need to install the Connector/C RPM packages. To install the Connector/C packages anyway, you must first remove the corresponding MySQL Server packages.

  • To install MySQL Server RPM packages if you already have Connector/C RPM packages installed, you must first remove the Connector/C RPM packages.

MySQL Server RPM packages other than shared and devel do not conflict with Connector/C packages and can be installed if Connector/C is installed. This includes the main server RPM that includes the mysqld server itself.

28.7.3 Example C API Client Programs

Many of the clients in MySQL source distributions are written in C, such as mysql , mysqladmin , and mysqlshow . If you are looking for examples that demonstrate how to use the C API, take a look at these clients: Obtain a source distribution and look in its client directory. See Section 2.1.2, “How to Get MySQL” .

28.7.4 Building and Running C API Client Programs

The following sections provide information on building client programs that use the C API. Topics include compiling and linking clients, writing threaded clients, and troubleshooting runtime problems.

28.7.4.1 Building C API Client Programs

This section provides guidelines for compiling C programs that use the MySQL C API.

Compiling MySQL Clients on Unix

The examples here use gcc as the compiler. A different compiler might be appropriate on some systems (for example, clang on OS X or FreeBSD, or Sun Studio on Solaris). Adjust the examples as necessary.

You may need to specify an -I option when you compile client programs that use MySQL header files, so that the compiler can find them. For example, if the header files are installed in /usr/local/mysql/include , use this option in the compile command:

-I/usr/local/mysql/include
                                

MySQL clients must be linked using the -lmysqlclient option in the link command. You may also need to specify a -L option to tell the linker where to find the library. For example, if the library is installed in /usr/local/mysql/lib , use these options in the link command:

-L/usr/local/mysql/lib -lmysqlclient
                                

The path names may differ on your system. Adjust the -I and -L options as necessary.

To make it simpler to compile MySQL programs on Unix, use the mysql_config script. See Section 4.7.1, “ mysql_config — Display Options for Compiling Clients” .

mysql_config displays the options needed for compiling or linking:

shell> mysql_config --cflags
shell> mysql_config --libs
                                

You can run those commands to get the proper options and add them manually to compilation or link commands. Alternatively, include the output from mysql_config directly within command lines using backticks:

shell> gcc -c `mysql_config --cflags` progname.c
shell> gcc -o progname progname.o `mysql_config --libs`
                                

On Unix, linking uses dynamic libraries by default. To link to the static client library instead, add its path name to the link command. For example, if the library is located in /usr/local/mysql/lib , link like this:

shell> gcc -o progname progname.o /usr/local/mysql/lib/libmysqlclient.a
                                

Or use mysql_config to provide the library name:

shell> gcc -o progname progname.o `mysql_config --variable=pkglibdir`/libmysqlclient.a
                                

mysql_config does not currently provide a way to list all libraries needed for static linking, so it might be necessary to name additional libraries on the link command (for example, -lnsl -lsocket on Solaris). To get an idea which libraries to add, use mysql_config --libs and ldd libmysqlclient.so (or otool -L libmysqlclient.dylib on OS X).

pkg-config can be used as an alternative to mysql_config for obtaining information such as compiler flags or link libraries required to compile MySQL applications. For example, the following pairs of commands are equivalent:

mysql_config --cflags
pkg-config --cflags mysqlclient
mysql_config --libs
pkg-config --libs mysqlclient
                                

To produce flags for static linking, use this command:

pkg-config --static --libs mysqlclient
                                

更多信息,请见 Section 28.7.4.2, “Building C API Client Programs Using pkg-config” .

Compiling MySQL Clients on Microsoft Windows

To specify header and library file locations, use the facilities provided by your development environment.

To build C API clients on Windows, you must link in the C client library, as well as the Windows ws2_32 sockets library and Secur32 security library.

You link your code with either the dynamic or static C client library. On Windows, the static library is named mysqlclient.lib and the dynamic library is named libmysql.dll . In addition, the libmysql.lib static import library is needed for using the dynamic library. If the static C client library is used, the client application must be compiled with the same version of Visual Studio used to compile the C client library (which is Visual Studio 2015 for the static C client library built by Oracle).

Note

The MySQL Connector/C is a standalone, drop-in replacement of the MySQL C client libraries that come with the MySQL server distribution. The Oracle-built MySQL Connector/C contains currently two versions of the static client library, one built with Visual Studio 2013 and the other one with Visual Studio 2015; use the one that matches the Visual Studio version you use to compile your application.

When using the Oracle-built MySQL C client library (or MySQL Connector/C), following these rules when it comes to linking the C runtime for your client application:

  • For the Community version of the MySQL C client library (or the Community version of MySQL Connector/C):

    • For version 8.0.0 (or MySQL Connector/C Community 6.1.9 and before):

      • If linking to the static C client library, link statically to the C runtime (use the /MT compiler option).

      • If linking to the dynamic C client library, link either statically or dynamically to the C runtime (use either /MT or /MD compiler option).

    • For version 8.0.1 and later (or MySQL Connector/C Community 6.1.10 and later): Always link dynamically to the C runtime (use the /MD compiler option), whether you are linking to the static or dynamic C client library. Also, target hosts running the client application need to have the Visual C++ Redistributable for Visual Studio 2015 installed.

  • For the Commercial version of the MySQL C client library (or the Commercial version of MySQL Connector/C):

    • If linking to the static C client library, link statically to the C runtime (use the /MT compiler option).

    • If linking to the dynamic C client library, link either statically or dynamically to the C runtime (use either /MT or /MD compiler option).

In general, when linking to a static MySQL C client library, the client library and the client application must use the same compiler option when it comes to linking the C runtime—that is, if your C client library is compiled with the /MT option, your client application should also be compiled with the /MT option, and so on (see the MSDN page describing the C library linking options for more details). Follow this rule when you are building your own static MySQL C client library (or MySQL Connector/C) from source and linking you client application to it.

Note

Debug Mode: Because of the above-mentioned rule, you cannot build your application in debug mode (with the /MTd or /MDd compiler option) and link it to the static C client library built by Oracle, which is not built with the debug options; instead, you will have to build the static client library from source with the debug options.

Troubleshooting Problems Linking to the MySQL Client Library

The MySQL client library includes SSL support built in. It is unnecessary to specify either -lssl or -lcrypto at link time. Doing so may in fact result in problems at runtime.

If the linker cannot find the MySQL client library, you might get undefined-reference errors for symbols that start with mysql_ , such as those shown here:

/tmp/ccFKsdPa.o: In function `main':
/tmp/ccFKsdPa.o(.text+0xb): undefined reference to `mysql_init'
/tmp/ccFKsdPa.o(.text+0x31): undefined reference to `mysql_real_connect'
/tmp/ccFKsdPa.o(.text+0x69): undefined reference to `mysql_error'
/tmp/ccFKsdPa.o(.text+0x9a): undefined reference to `mysql_close'
                                

You should be able to solve this problem by adding -L dir_path -lmysqlclient at the end of your link command, where dir_path represents the path name of the directory where the client library is located. To determine the correct directory, try this command:

shell> mysql_config --libs
                                

The output from mysql_config might indicate other libraries that should be specified on the link command as well. You can include mysql_config output directly in your compile or link command using backticks. For example:

shell> gcc -o progname progname.o `mysql_config --libs`
                                

If an error occurs at link time that the floor symbol is undefined, link to the math library by adding -lm to the end of the compile/link line. Similarly, if you get undefined-reference errors for other functions that should exist on your system, such as connect() , check the manual page for the function in question to determine which libraries you should add to the link command.

If you get undefined-reference errors such as the following for functions that do not exist on your system, it usually means that your MySQL client library was compiled on a system that is not 100% compatible with yours:

mf_format.o(.text+0x201): undefined reference to `__lxstat'
                                

In this case, you should download the latest MySQL or Connector/C source distribution and compile the MySQL client library yourself. See Section 2.9, “Installing MySQL from Source” , and MySQL Connector/C Developer Guide .

28.7.4.2 Building C API Client Programs Using pkg-config

MySQL distributions contain a mysqlclient.pc file that provides information about MySQL configuration for use by the pkg-config command. This enables pkg-config to be used as an alternative to mysql_config for obtaining information such as compiler flags or link libraries required to compile MySQL applications. For example, the following pairs of commands are equivalent:

mysql_config --cflags
pkg-config --cflags mysqlclient
mysql_config --libs
pkg-config --libs mysqlclient
                            

The last pkg-config command produces flags for dynamic linking. To produce flags for static linking, use this command:

pkg-config --static --libs mysqlclient
                            

On some platforms, the output with and without --static might be the same.

Note

If pkg-config does not find MySQL information, it might be necessary to set the PKG_CONFIG_PATH environment variable to the directory in which the mysqlclient.pc file is located, which by default is usually the pkgconfig directory under the MySQL library directory. For example (adjust the location appropriately):

export PKG_CONFIG_PATH=/usr/local/mysql/lib/pkgconfig # sh, bash, ...
setenv PKG_CONFIG_PATH /usr/local/mysql/lib/pkgconfig # csh, tcsh, ...
                                

The mysqlconfig.pc installation location can be controlled using the INSTALL_PKGCONFIGDIR CMake option. See Section 2.9.4, “MySQL Source-Configuration Options” .

The --variable option takes a configuration variable name and displays the variable value:

pkg-config --variable=prefix mysqlclient     # installation prefix directory
pkg-config --variable=includedir mysqlclient # header file directory
pkg-config --variable=libdir mysqlclient     # library directory
                            

To see which variable values pkg-config can display using the --variable option, use this command:

pkg-config --print-variables mysqlclient
                            

You can use pkg-config within a command line using backticks to include the output that it produces for particular options. For example, to compile and link a MySQL client program, use pkg-config as follows:

gcc -c `pkg-config --cflags mysqlclient` progname.c
gcc -o progname progname.o `pkg-config --libs mysqlclient`
                            

28.7.4.3 Writing C API Threaded Client Programs

The client library is almost thread-safe. The biggest problem is that the subroutines in sql/net_serv.cc that read from sockets are not interrupt-safe. This was done with the thought that you might want to have your own alarm that can break a long read to a server. If you install interrupt handlers for the SIGPIPE interrupt, socket handling should be thread-safe.

To avoid aborting the program when a connection terminates, MySQL blocks SIGPIPE on the first call to mysql_library_init() , mysql_init() , or mysql_connect() . To use your own SIGPIPE handler, first call mysql_library_init() , then install your handler.

If undefined symbol errors occur when linking against the libmysqlclient client library, in most cases this is because you have not included the thread libraries on the link/compile command.

The client library is thread-safe per connection. You can let two threads share the same connection with the following caveats:

  • Multiple threads cannot send a query to the MySQL server at the same time on the same connection. In particular, you must ensure that between calls to mysql_query() and mysql_store_result() in one thread, no other thread uses the same connection. You must have a mutex lock around your pair of mysql_query() and mysql_store_result() calls. After mysql_store_result() returns, the lock can be released and other threads may query the same connection.

    If you use POSIX threads, you can use pthread_mutex_lock() and pthread_mutex_unlock() to establish and release a mutex lock.

  • Many threads can access different result sets that are retrieved with mysql_store_result() .

  • To use mysql_use_result() , you must ensure that no other thread is using the same connection until the result set is closed. However, it really is best for threaded clients that share the same connection to use mysql_store_result() .

You need to know the following if you have a thread that did not create the connection to the MySQL database but is calling MySQL functions:

When you call mysql_init() , MySQL creates a thread-specific variable for the thread that is used by the debug library (among other things). If you call a MySQL function before the thread has called mysql_init() , the thread does not have the necessary thread-specific variables in place and you are likely to end up with a core dump sooner or later. To avoid problems, you must do the following:

  1. Call mysql_library_init() before any other MySQL functions. It is not thread-safe, so call it before threads are created, or protect the call with a mutex.

  2. Arrange for mysql_thread_init() to be called early in the thread handler before calling any MySQL function. If you call mysql_init() , it will call mysql_thread_init() for you.

  3. In the thread, call mysql_thread_end() before calling pthread_exit() . This frees the memory used by MySQL thread-specific variables.

The preceding notes regarding mysql_init() also apply to mysql_connect() , which calls mysql_init() .

28.7.4.4 Running C API Client Programs

If, after an upgrade, you experience problems with compiled client programs, such as Commands out of sync or unexpected core dumps, the programs were probably compiled using old header or library files. In this case, check the date of the mysql.h file and libmysqlclient.a library used for compilation to verify that they are from the new MySQL distribution. If not, recompile the programs with the new headers and libraries. Recompilation might also be necessary for programs compiled against the shared client library if the library major version number has changed (for example, from libmysqlclient.so.17 to libmysqlclient.so.18 ).

The major client library version determines compatibility. (For example, for libmysqlclient.so.18.1.0 , the major version is 18.) For this reason, the libraries shipped with newer versions of MySQL are drop-in replacements for older versions that have the same major number. As long as the major library version is the same, you can upgrade the library and old applications should continue to work with it.

Undefined-reference errors might occur at runtime when you try to execute a MySQL program. If these errors specify symbols that start with mysql_ or indicate that the libmysqlclient library cannot be found, it means that your system cannot find the shared libmysqlclient.so library. The solution to this problem is to tell your system to search for shared libraries in the directory where that library is located. Use whichever of the following methods is appropriate for your system:

  • Add the path of the directory where libmysqlclient.so is located to the LD_LIBRARY_PATH or LD_LIBRARY environment variable.

  • On OS X, add the path of the directory where libmysqlclient.dylib is located to the DYLD_LIBRARY_PATH environment variable.

  • Copy the shared-library files (such as libmysqlclient.so ) to some directory that is searched by your system, such as /lib , and update the shared library information by executing ldconfig . Be sure to copy all related files. A shared library might exist under several names, using symlinks to provide the alternate names.

28.7.4.5 C API Server and Client Library Versions

The string and numeric forms of the MySQL server version are available at compile time as the values of the MYSQL_SERVER_VERSION and MYSQL_VERSION_ID macros, and at runtime as the values of the mysql_get_server_info() and mysql_get_server_version() functions.

The MySQL client library version depends on the type of distribution that provides the library:

  • For MySQL distributions, the client library version is the MySQL version. The string and numeric forms of this version are available at compile time as the values of the MYSQL_SERVER_VERSION and MYSQL_VERSION_ID macros, and at runtime as the values of the mysql_get_client_info() and mysql_get_client_version() functions.

    The LIBMYSQL_VERSION and LIBMYSQL_VERSION_ID macros have the same values as MYSQL_SERVER_VERSION and MYSQL_VERSION_ID and the two sets of macros can be used interchangeably.

  • For Connector/C distributions, the client library version is the Connector/C version. The string and numeric forms of this version are available at compile time as the values of the LIBMYSQL_VERSION and LIBMYSQL_VERSION_ID macros, and at runtime as the values of the mysql_get_client_info() and mysql_get_client_version() functions.

    The MYSQL_SERVER_VERSION and MYSQL_VERSION_ID macros indicate the string and numeric forms of the MySQL version on which the Connector/C distribution is based.

28.7.5 C API Data Structures

This section describes C API data structures other than those used for prepared statements or the replication stream interface. For information about those, see Section 28.7.9, “C API Prepared Statement Data Structures” , and Section 28.7.15, “C API Binary Log Data Structures” .

  • MYSQL

    This structure represents handler for one database connection. It is used for almost all MySQL functions. Do not try to make a copy of a MYSQL structure. There is no guarantee that such a copy will be usable.

  • MYSQL_RES

    This structure represents the result of a query that returns rows ( SELECT , SHOW , DESCRIBE , EXPLAIN ). The information returned from a query is called the result set in the remainder of this section.

  • MYSQL_ROW

    This is a type-safe representation of one row of data. It is currently implemented as an array of counted byte strings. (You cannot treat these as null-terminated strings if field values may contain binary data, because such values may contain null bytes internally.) Rows are obtained by calling mysql_fetch_row() .

  • MYSQL_FIELD

    This structure contains metadata: information about a field, such as the field's name, type, and size. Its members are described in more detail later in this section. You may obtain the MYSQL_FIELD structures for each field by calling mysql_fetch_field() repeatedly. Field values are not part of this structure; they are contained in a MYSQL_ROW structure.

  • MYSQL_FIELD_OFFSET

    This is a type-safe representation of an offset into a MySQL field list. (Used by mysql_field_seek() .) Offsets are field numbers within a row, beginning at zero.

  • my_ulonglong

    The type used for the number of rows and for mysql_affected_rows() , mysql_num_rows() , and mysql_insert_id() . This type provides a range of 0 to 1.84e19 .

    Some functions that return a row count using this type return -1 as an unsigned value to indicate an error or exceptional condition. You can check for -1 by comparing the return value to (my_ulonglong)-1 (or to (my_ulonglong)~0 , which is equivalent).

    On some systems, attempting to print a value of type my_ulonglong does not work. To print such a value, convert it to unsigned long and use a %lu print format. Example:

    printf ("Number of rows: %lu\n",
            (unsigned long) mysql_num_rows(result));
                                        
  • my_bool

    A boolean type, for values that are true (nonzero) or false (zero). The my_bool type was used before MySQL 8.0. As of MySQL 8.0, use the bool or int C type instead.

    Note

    The change from my_bool to bool means that the mysql.h header file requires a C++ or C99 compiler to compile.

The MYSQL_FIELD structure contains the members described in the following list. The definitions apply primarily for columns of result sets such as those produced by SELECT statements. MYSQL_FIELD structures are also used to provide metadata for OUT and INOUT parameters returned from stored procedures executed using prepared CALL statements. For such parameters, some of the structure members have a meaning different from the meaning for column values.

  • char * name

    The name of the field, as a null-terminated string. If the field was given an alias with an AS clause, the value of name is the alias. For a procedure parameter, the parameter name.

  • char * org_name

    The name of the field, as a null-terminated string. Aliases are ignored. For expressions, the value is an empty string. For a procedure parameter, the parameter name.

  • char * table

    The name of the table containing this field, if it is not a calculated field. For calculated fields, the table value is an empty string. If the column is selected from a view, table names the view. If the table or view was given an alias with an AS clause, the value of table is the alias. For a UNION , the value is the empty string. For a procedure parameter, the procedure name.

  • char * org_table

    The name of the table, as a null-terminated string. Aliases are ignored. If the column is selected from a view, org_table names the view. If the column is selected from a derived table, org_table names the base table. If a derived table wraps a view, org_table still names the base table. If the column is an expression, org_table is the empty string. For a UNION , the value is the empty string. For a procedure parameter, the value is the procedure name.

  • char * db

    The name of the database that the field comes from, as a null-terminated string. If the field is a calculated field, db is an empty string. For a UNION , the value is the empty string. For a procedure parameter, the name of the database containing the procedure.

  • char * catalog

    The catalog name. This value is always "def" .

  • char * def

    The default value of this field, as a null-terminated string. This is set only if you use mysql_list_fields() .

  • unsigned long length

    The width of the field. This corresponds to the display length, in bytes.

    The server determines the length value before it generates the result set, so this is the minimum length required for a data type capable of holding the largest possible value from the result column, without knowing in advance the actual values that will be produced by the query for the result set.

  • unsigned long max_length

    The maximum width of the field for the result set (the length in bytes of the longest field value for the rows actually in the result set). If you use mysql_store_result() or mysql_list_fields() , this contains the maximum length for the field. If you use mysql_use_result() , the value of this variable is zero.

    The value of max_length is the length of the string representation of the values in the result set. For example, if you retrieve a FLOAT column and the widest value is -12.345 , max_length is 7 (the length of '-12.345' ).

    If you are using prepared statements, max_length is not set by default because for the binary protocol the lengths of the values depend on the types of the values in the result set. (See Section 28.7.9, “C API Prepared Statement Data Structures” .) If you want the max_length values anyway, enable the STMT_ATTR_UPDATE_MAX_LENGTH option with mysql_stmt_attr_set() and the lengths will be set when you call mysql_stmt_store_result() . (See Section 28.7.11.3, “mysql_stmt_attr_set()” , and Section 28.7.11.28, “mysql_stmt_store_result()” .)

  • unsigned int name_length

    The length of name .

  • unsigned int org_name_length

    The length of org_name .

  • unsigned int table_length

    The length of table .

  • unsigned int org_table_length

    The length of org_table .

  • unsigned int db_length

    The length of db .

  • unsigned int catalog_length

    The length of catalog .

  • unsigned int def_length

    The length of def .

  • unsigned int flags

    Bit-flags that describe the field. The flags value may have zero or more of the bits set that are shown in the following table.

    Flag Value Flag Description
    NOT_NULL_FLAG Field cannot be NULL
    PRI_KEY_FLAG Field is part of a primary key
    UNIQUE_KEY_FLAG Field is part of a unique key
    MULTIPLE_KEY_FLAG Field is part of a nonunique key
    UNSIGNED_FLAG Field has the UNSIGNED attribute
    ZEROFILL_FLAG Field has the ZEROFILL attribute
    BINARY_FLAG Field has the BINARY attribute
    AUTO_INCREMENT_FLAG Field has the AUTO_INCREMENT attribute
    ENUM_FLAG Field is an ENUM
    SET_FLAG Field is a SET
    BLOB_FLAG Field is a BLOB or TEXT (deprecated)
    TIMESTAMP_FLAG Field is a TIMESTAMP (deprecated)
    NUM_FLAG Field is numeric; see additional notes following table
    NO_DEFAULT_VALUE_FLAG Field has no default value; see additional notes following table

    Some of these flags indicate data type information and are superseded by or used in conjunction with the MYSQL_TYPE_ xxx value in the field->type member described later:

    • To check for BLOB or TIMESTAMP values, check whether type is MYSQL_TYPE_BLOB or MYSQL_TYPE_TIMESTAMP . (The BLOB_FLAG and TIMESTAMP_FLAG flags are unneeded.)

    • ENUM and SET values are returned as strings. For these, check that the type value is MYSQL_TYPE_STRING and that the ENUM_FLAG or SET_FLAG flag is set in the flags value.

    NUM_FLAG indicates that a column is numeric. This includes columns with a type of MYSQL_TYPE_DECIMAL , MYSQL_TYPE_NEWDECIMAL , MYSQL_TYPE_TINY , MYSQL_TYPE_SHORT , MYSQL_TYPE_LONG , MYSQL_TYPE_FLOAT , MYSQL_TYPE_DOUBLE , MYSQL_TYPE_NULL , MYSQL_TYPE_LONGLONG , MYSQL_TYPE_INT24 , and MYSQL_TYPE_YEAR .

    NO_DEFAULT_VALUE_FLAG indicates that a column has no DEFAULT clause in its definition. This does not apply to NULL columns (because such columns have a default of NULL ), or to AUTO_INCREMENT columns (which have an implied default value).

    The following example illustrates a typical use of the flags value:

    if (field->flags & NOT_NULL_FLAG)
        printf("Field cannot be null\n");
                                        

    You may use the convenience macros shown in the following table to determine the boolean status of the flags value.

    Flag Status 描述
    IS_NOT_NULL(flags) True if this field is defined as NOT NULL
    IS_PRI_KEY(flags) True if this field is a primary key
    IS_BLOB(flags) True if this field is a BLOB or TEXT (deprecated; test field->type instead)
  • unsigned int decimals

    The number of decimals for numeric fields, and the fractional seconds precision for temporal fields.

  • unsigned int charsetnr

    An ID number that indicates the character set/collation pair for the field.

    Normally, character values in result sets are converted to the character set indicated by the character_set_results system variable. In this case, charsetnr corresponds to the character set indicated by that variable. Character set conversion can be suppressed by setting character_set_results to NULL . In this case, charsetnr corresponds to the character set of the original table column or expression. See also Section 10.4, “Connection Character Sets and Collations” .

    To distinguish between binary and nonbinary data for string data types, check whether the charsetnr value is 63. If so, the character set is binary , which indicates binary rather than nonbinary data. This enables you to distinguish BINARY from CHAR , VARBINARY from VARCHAR , and the BLOB types from the TEXT types.

    charsetnr values are the same as those displayed in the Id column of the SHOW COLLATION statement or the ID column of the INFORMATION_SCHEMA COLLATIONS table. You can use those information sources to see which character set and collation specific charsetnr values indicate:

    mysql> SHOW COLLATION WHERE Id = 63;
    +-----------+---------+----+---------+----------+---------+
    | Collation | Charset | Id | Default | Compiled | Sortlen |
    +-----------+---------+----+---------+----------+---------+
    | binary    | binary  | 63 | Yes     | Yes      |       1 |
    +-----------+---------+----+---------+----------+---------+
    mysql> SELECT COLLATION_NAME, CHARACTER_SET_NAME
           FROM INFORMATION_SCHEMA.COLLATIONS WHERE ID = 33;
    +-----------------+--------------------+
    | COLLATION_NAME  | CHARACTER_SET_NAME |
    +-----------------+--------------------+
    | utf8_general_ci | utf8               |
    +-----------------+--------------------+
                                        
  • enum enum_field_types type

    The type of the field. The type value may be one of the MYSQL_TYPE_ symbols shown in the following table.

    Type Value Type Description
    MYSQL_TYPE_TINY TINYINT field
    MYSQL_TYPE_SHORT SMALLINT field
    MYSQL_TYPE_LONG INTEGER field
    MYSQL_TYPE_INT24 MEDIUMINT field
    MYSQL_TYPE_LONGLONG BIGINT field
    MYSQL_TYPE_DECIMAL DECIMAL or NUMERIC field
    MYSQL_TYPE_NEWDECIMAL Precision math DECIMAL or NUMERIC
    MYSQL_TYPE_FLOAT FLOAT field
    MYSQL_TYPE_DOUBLE DOUBLE or REAL field
    MYSQL_TYPE_BIT BIT field
    MYSQL_TYPE_TIMESTAMP TIMESTAMP field
    MYSQL_TYPE_DATE DATE field
    MYSQL_TYPE_TIME TIME field
    MYSQL_TYPE_DATETIME DATETIME field
    MYSQL_TYPE_YEAR YEAR field
    MYSQL_TYPE_STRING CHAR or BINARY field
    MYSQL_TYPE_VAR_STRING VARCHAR or VARBINARY field
    MYSQL_TYPE_BLOB BLOB or TEXT field (use max_length to determine the maximum length)
    MYSQL_TYPE_SET SET field
    MYSQL_TYPE_ENUM ENUM field
    MYSQL_TYPE_GEOMETRY Spatial field
    MYSQL_TYPE_NULL NULL -type field

    The MYSQL_TYPE_TIME2 , MYSQL_TYPE_DATETIME2 , and MYSQL_TYPE_TIMESTAMP2 ) type codes are used only on the server side. Clients see the MYSQL_TYPE_TIME , MYSQL_TYPE_DATETIME , and MYSQL_TYPE_TIMESTAMP codes.

    You can use the IS_NUM() macro to test whether a field has a numeric type. Pass the type value to IS_NUM() and it evaluates to TRUE if the field is numeric:

    if (IS_NUM(field->type))
        printf("Field is numeric\n");
                                        

    ENUM and SET values are returned as strings. For these, check that the type value is MYSQL_TYPE_STRING and that the ENUM_FLAG or SET_FLAG flag is set in the flags value.

28.7.6 C API Function Overview

The following list summarizes the functions available in the C API. For greater detail, see the descriptions in Section 28.7.7, “C API Function Descriptions” .

Application programs should use this general outline for interacting with MySQL:

  1. Initialize the MySQL client library by calling mysql_library_init() .

  2. Initialize a connection handler by calling mysql_init() and connect to the server by calling mysql_real_connect() .

  3. Issue SQL statements and process their results. (The following discussion provides more information about how to do this.)

  4. Close the connection to the MySQL server by calling mysql_close() .

  5. End use of the MySQL client library by calling mysql_library_end() .

The purpose of calling mysql_library_init() and mysql_library_end() is to provide proper initialization and finalization of the MySQL client library. For applications that are linked with the client library, they provide improved memory management. If you do not call mysql_library_end() , a block of memory remains allocated. (This does not increase the amount of memory used by the application, but some memory leak detectors will complain about it.)

In a nonmultithreaded environment, the call to mysql_library_init() may be omitted, because mysql_init() will invoke it automatically as necessary. However, mysql_library_init() is not thread-safe in a multithreaded environment, and thus neither is mysql_init() , which calls mysql_library_init() . You must either call mysql_library_init() prior to spawning any threads, or else use a mutex to protect the call, whether you invoke mysql_library_init() or indirectly through mysql_init() . This should be done prior to any other client library call.

To connect to the server, call mysql_init() to initialize a connection handler, then call mysql_real_connect() with that handler (along with other information such as the host name, user name, and password). Upon connection, mysql_real_connect() sets the reconnect flag (part of the MYSQL structure) to a value of 1 in versions of the API older than 5.0.3, or 0 in newer versions. A value of 1 for this flag indicates that if a statement cannot be performed because of a lost connection, to try reconnecting to the server before giving up. You can use the MYSQL_OPT_RECONNECT option to mysql_options() to control reconnection behavior. When you are done with the connection, call mysql_close() to terminate it.

While a connection is active, the client may send SQL statements to the server using mysql_query() or mysql_real_query() . The difference between the two is that mysql_query() expects the query to be specified as a null-terminated string whereas mysql_real_query() expects a counted string. If the string contains binary data (which may include null bytes), you must use mysql_real_query() .

For each non- SELECT query (for example, INSERT , UPDATE , DELETE ), you can find out how many rows were changed (affected) by calling mysql_affected_rows() .

For SELECT queries, you retrieve the selected rows as a result set. (Note that some statements are SELECT -like in that they return rows. These include SHOW , DESCRIBE , and EXPLAIN . Treat these statements the same way as SELECT statements.)

There are two ways for a client to process result sets. One way is to retrieve the entire result set all at once by calling mysql_store_result() . This function acquires from the server all the rows returned by the query and stores them in the client. The second way is for the client to initiate a row-by-row result set retrieval by calling mysql_use_result() . This function initializes the retrieval, but does not actually get any rows from the server.

In both cases, you access rows by calling mysql_fetch_row() . With mysql_store_result() , mysql_fetch_row() accesses rows that have previously been fetched from the server. With mysql_use_result() , mysql_fetch_row() actually retrieves the row from the server. Information about the size of the data in each row is available by calling mysql_fetch_lengths() .

After you are done with a result set, call mysql_free_result() to free the memory used for it.

The two retrieval mechanisms are complementary. Choose the approach that is most appropriate for each client application. In practice, clients tend to use mysql_store_result() more commonly.

An advantage of mysql_store_result() is that because the rows have all been fetched to the client, you not only can access rows sequentially, you can move back and forth in the result set using mysql_data_seek() or mysql_row_seek() to change the current row position within the result set. You can also find out how many rows there are by calling mysql_num_rows() . On the other hand, the memory requirements for mysql_store_result() may be very high for large result sets and you are more likely to encounter out-of-memory conditions.

An advantage of mysql_use_result() is that the client requires less memory for the result set because it maintains only one row at a time (and because there is less allocation overhead, mysql_use_result() can be faster). Disadvantages are that you must process each row quickly to avoid tying up the server, you do not have random access to rows within the result set (you can only access rows sequentially), and the number of rows in the result set is unknown until you have retrieved them all. Furthermore, you must retrieve all the rows even if you determine in mid-retrieval that you've found the information you were looking for.

The API makes it possible for clients to respond appropriately to statements (retrieving rows only as necessary) without knowing whether the statement is a SELECT . You can do this by calling mysql_store_result() after each mysql_query() (or mysql_real_query() ). If the result set call succeeds, the statement was a SELECT and you can read the rows. If the result set call fails, call mysql_field_count() to determine whether a result was actually to be expected. If mysql_field_count() returns zero, the statement returned no data (indicating that it was an INSERT , UPDATE , DELETE , and so forth), and was not expected to return rows. If mysql_field_count() is nonzero, the statement should have returned rows, but did not. This indicates that the statement was a SELECT that failed. See the description for mysql_field_count() for an example of how this can be done.

Both mysql_store_result() and mysql_use_result() enable you to obtain information about the fields that make up the result set (the number of fields, their names and types, and so forth). You can access field information sequentially within the row by calling mysql_fetch_field() repeatedly, or by field number within the row by calling mysql_fetch_field_direct() . The current field cursor position may be changed by calling mysql_field_seek() . Setting the field cursor affects subsequent calls to mysql_fetch_field() . You can also get information for fields all at once by calling mysql_fetch_fields() .

For detecting and reporting errors, MySQL provides access to error information by means of the mysql_errno() and mysql_error() functions. These return the error code or error message for the most recently invoked function that can succeed or fail, enabling you to determine when an error occurred and what it was.

28.7.7 C API Function Descriptions

28.7.7.1 mysql_affected_rows()
28.7.7.2 mysql_autocommit()
28.7.7.3 mysql_change_user()
28.7.7.4 mysql_character_set_name()
28.7.7.5 mysql_close()
28.7.7.6 mysql_commit()
28.7.7.7 mysql_connect()
28.7.7.8 mysql_create_db()
28.7.7.9 mysql_data_seek()
28.7.7.10 mysql_debug()
28.7.7.11 mysql_drop_db()
28.7.7.12 mysql_dump_debug_info()
28.7.7.13 mysql_eof()
28.7.7.14 mysql_errno()
28.7.7.15 mysql_error()
28.7.7.16 mysql_escape_string()
28.7.7.17 mysql_fetch_field()
28.7.7.18 mysql_fetch_field_direct()
28.7.7.19 mysql_fetch_fields()
28.7.7.20 mysql_fetch_lengths()
28.7.7.21 mysql_fetch_row()
28.7.7.22 mysql_field_count()
28.7.7.23 mysql_field_seek()
28.7.7.24 mysql_field_tell()
28.7.7.25 mysql_free_result()
28.7.7.26 mysql_get_character_set_info()
28.7.7.27 mysql_get_client_info()
28.7.7.28 mysql_get_client_version()
28.7.7.29 mysql_get_host_info()
28.7.7.30 mysql_get_option()
28.7.7.31 mysql_get_proto_info()
28.7.7.32 mysql_get_server_info()
28.7.7.33 mysql_get_server_version()
28.7.7.34 mysql_get_ssl_cipher()
28.7.7.35 mysql_hex_string()
28.7.7.36 mysql_info()
28.7.7.37 mysql_init()
28.7.7.38 mysql_insert_id()
28.7.7.39 mysql_kill()
28.7.7.40 mysql_library_end()
28.7.7.41 mysql_library_init()
28.7.7.42 mysql_list_dbs()
28.7.7.43 mysql_list_fields()
28.7.7.44 mysql_list_processes()
28.7.7.45 mysql_list_tables()
28.7.7.46 mysql_more_results()
28.7.7.47 mysql_next_result()
28.7.7.48 mysql_num_fields()
28.7.7.49 mysql_num_rows()
28.7.7.50 mysql_options()
28.7.7.51 mysql_options4()
28.7.7.52 mysql_ping()
28.7.7.53 mysql_query()
28.7.7.54 mysql_real_connect()
28.7.7.55 mysql_real_escape_string()
28.7.7.56 mysql_real_escape_string_quote()
28.7.7.57 mysql_real_query()
28.7.7.58 mysql_refresh()
28.7.7.59 mysql_reload()
28.7.7.60 mysql_reset_connection()
28.7.7.61 mysql_reset_server_public_key()
28.7.7.62 mysql_result_metadata()
28.7.7.63 mysql_rollback()
28.7.7.64 mysql_row_seek()
28.7.7.65 mysql_row_tell()
28.7.7.66 mysql_select_db()
28.7.7.67 mysql_server_end()
28.7.7.68 mysql_server_init()
28.7.7.69 mysql_session_track_get_first()
28.7.7.70 mysql_session_track_get_next()
28.7.7.71 mysql_set_character_set()
28.7.7.72 mysql_set_local_infile_default()
28.7.7.73 mysql_set_local_infile_handler()
28.7.7.74 mysql_set_server_option()
28.7.7.75 mysql_shutdown()
28.7.7.76 mysql_sqlstate()
28.7.7.77 mysql_ssl_set()
28.7.7.78 mysql_stat()
28.7.7.79 mysql_store_result()
28.7.7.80 mysql_thread_id()
28.7.7.81 mysql_use_result()
28.7.7.82 mysql_warning_count()

In the descriptions here, a parameter or return value of NULL means NULL in the sense of the C programming language, not a MySQL NULL value.

Functions that return a value generally return a pointer or an integer. Unless specified otherwise, functions returning a pointer return a non- NULL value to indicate success or a NULL value to indicate an error, and functions returning an integer return zero to indicate success or nonzero to indicate an error. Note that nonzero means just that. Unless the function description says otherwise, do not test against a value other than zero:

if (result)                   /* correct */
    ... error ...
if (result < 0)               /* incorrect */
    ... error ...
if (result == -1)             /* incorrect */
    ... error ...
                        

When a function returns an error, the Errors subsection of the function description lists the possible types of errors. You can find out which of these occurred by calling mysql_errno() . A string representation of the error may be obtained by calling mysql_error() .

28.7.7.1 mysql_affected_rows()

my_ulonglong mysql_affected_rows(MYSQL *mysql)

描述

mysql_affected_rows() may be called immediately after executing a statement with mysql_query() or mysql_real_query() . It returns the number of rows changed, deleted, or inserted by the last statement if it was an UPDATE , DELETE , or INSERT . For SELECT statements, mysql_affected_rows() works like mysql_num_rows() .

For UPDATE statements, the affected-rows value by default is the number of rows actually changed. If you specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld , the affected-rows value is the number of rows found ; that is, matched by the WHERE clause.

For REPLACE statements, the affected-rows value is 2 if the new row replaced an old row, because in this case, one row was inserted after the duplicate was deleted.

For INSERT ... ON DUPLICATE KEY UPDATE statements, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag, the affected-rows value is 1 (not 0) if an existing row is set to its current values.

Following a CALL statement for a stored procedure, mysql_affected_rows() returns the value that it would return for the last statement executed within the procedure, or 0 if that statement would return -1 . Within the procedure, you can use ROW_COUNT() at the SQL level to obtain the affected-rows value for individual statements.

mysql_affected_rows() returns a meaningful value for a wide range of statements. For details, see the description for ROW_COUNT() in Section 12.15, “Information Functions” .

Return Values

An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records were updated for an UPDATE statement, no rows matched the WHERE clause in the query or that no query has yet been executed. -1 indicates that the query returned an error or that, for a SELECT query, mysql_affected_rows() was called prior to calling mysql_store_result() .

Because mysql_affected_rows() returns an unsigned value, you can check for -1 by comparing the return value to (my_ulonglong)-1 (or to (my_ulonglong)~0 , which is equivalent).

Errors

None.

Example
char *stmt = "UPDATE products SET cost=cost*1.25
              WHERE group=10";
mysql_query(&mysql,stmt);
printf("%ld products updated",
       (long) mysql_affected_rows(&mysql));
                                

28.7.7.2 mysql_autocommit()

bool mysql_autocommit(MYSQL *mysql, bool mode)

描述

Sets autocommit mode on if mode is 1, off if mode is 0.

Return Values

Zero for success. Nonzero if an error occurred.

Errors

None.

28.7.7.3 mysql_change_user()

bool mysql_change_user(MYSQL *mysql, const char *user, const char *password, const char *db)

描述

Changes the user and causes the database specified by db to become the default (current) database on the connection specified by mysql . In subsequent queries, this database is the default for table references that include no explicit database specifier.

mysql_change_user() fails if the connected user cannot be authenticated or does not have permission to use the database. In this case, the user and database are not changed.

Pass a db parameter of NULL if you do not want to have a default database.

This function resets the session state as if one had done a new connect and reauthenticated. (See Section 28.7.24, “C API Automatic Reconnection Control” .) It always performs a ROLLBACK of any active transactions, closes and drops all temporary tables, and unlocks all locked tables. Session system variables are reset to the values of the corresponding global system variables. Prepared statements are released and HANDLER variables are closed. Locks acquired with GET_LOCK() are released. These effects occur even if the user did not change.

To reset the connection state in a more lightweight manner without changing the user, use mysql_reset_connection() .

Return Values

Zero for success. Nonzero if an error occurred.

Errors

The same that you can get from mysql_real_connect() , plus:

Example
if (mysql_change_user(&mysql, "user", "password", "new_database"))
{
   fprintf(stderr, "Failed to change user.  Error: %s\n",
           mysql_error(&mysql));
}
                                

28.7.7.4 mysql_character_set_name()

const char *mysql_character_set_name(MYSQL *mysql)

描述

Returns the default character set name for the current connection.

Return Values

The default character set name

Errors

None.

28.7.7.5 mysql_close()

void mysql_close(MYSQL *mysql)

描述

Closes a previously opened connection. mysql_close() also deallocates the connection handler pointed to by mysql if the handler was allocated automatically by mysql_init() or mysql_connect() .

Return Values

None.

Errors

None.

28.7.7.6 mysql_commit()

bool mysql_commit(MYSQL *mysql)

描述

Commits the current transaction.

The action of this function is subject to the value of the completion_type system variable. In particular, if the value of completion_type is RELEASE (or 2), the server performs a release after terminating a transaction and closes the client connection. Call mysql_close() from the client program to close the connection from the client side.

Return Values

Zero for success. Nonzero if an error occurred.

Errors

None.

28.7.7.7 mysql_connect()

MYSQL *mysql_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd)

描述

This function is deprecated. Use mysql_real_connect() instead.

mysql_connect() attempts to establish a connection to a MySQL database engine running on host . mysql_connect() must complete successfully before you can execute any of the other API functions, with the exception of mysql_get_client_info() .

The meanings of the parameters are the same as for the corresponding parameters for mysql_real_connect() with the difference that the connection parameter may be NULL . In this case, the C API allocates memory for the connection structure automatically and frees it when you call mysql_close() . The disadvantage of this approach is that you cannot retrieve an error message if the connection fails. (To get error information from mysql_errno() or mysql_error() , you must provide a valid MYSQL pointer.)

Return Values

Same as for mysql_real_connect() .

Errors

Same as for mysql_real_connect() .

28.7.7.8 mysql_create_db()

int mysql_create_db(MYSQL *mysql, const char *db)

描述

Creates the database named by the db parameter.

This function is deprecated. Use mysql_query() to issue an SQL CREATE DATABASE statement instead.

Return Values

Zero for success. Nonzero if an error occurred.

Errors
Example
if(mysql_create_db(&mysql, "my_database"))
{
   fprintf(stderr, "Failed to create new database.  Error: %s\n",
           mysql_error(&mysql));
}
                                

28.7.7.9 mysql_data_seek()

void mysql_data_seek(MYSQL_RES *result, my_ulonglong offset)

描述

Seeks to an arbitrary row in a query result set. The offset value is a row number. Specify a value in the range from 0 to mysql_num_rows(result)-1 .

This function requires that the result set structure contains the entire result of the query, so mysql_data_seek() may be used only in conjunction with mysql_store_result() , not with mysql_use_result() .

Return Values

None.

Errors

None.

28.7.7.10 mysql_debug()

void mysql_debug(const char *debug)

描述

Does a DBUG_PUSH with the given string. mysql_debug() uses the Fred Fish debug library. To use this function, you must compile the client library to support debugging. See Section 29.5.3, “The DBUG Package” .

Return Values

None.

Errors

None.

Example

The call shown here causes the client library to generate a trace file in /tmp/client.trace on the client machine:

mysql_debug("d:t:O,/tmp/client.trace");
                                

28.7.7.11 mysql_drop_db()

int mysql_drop_db(MYSQL *mysql, const char *db)

描述

Drops the database named by the db parameter.

This function is deprecated. Use mysql_query() to issue an SQL DROP DATABASE statement instead.

Return Values

Zero for success. Nonzero if an error occurred.

Errors
Example
if(mysql_drop_db(&mysql, "my_database"))
  fprintf(stderr, "Failed to drop the database: Error: %s\n",
          mysql_error(&mysql));
                                

28.7.7.12 mysql_dump_debug_info()

int mysql_dump_debug_info(MYSQL *mysql)

描述

Instructs the server to write debugging information to the error log. The connected user must have the SUPER privilege.

Return Values

Zero for success. Nonzero if an error occurred.

Errors

28.7.7.13 mysql_eof()

bool mysql_eof(MYSQL_RES *result)

描述

This function is deprecated. mysql_errno() or mysql_error() may be used instead.

mysql_eof() determines whether the last row of a result set has been read.

If you acquire a result set from a successful call to mysql_store_result() , the client receives the entire set in one operation. In this case, a NULL return from mysql_fetch_row() always means the end of the result set has been reached and it is unnecessary to call mysql_eof() . When used with mysql_store_result() , mysql_eof() always returns true.

On the other hand, if you use mysql_use_result() to initiate a result set retrieval, the rows of the set are obtained from the server one by one as you call mysql_fetch_row() repeatedly. Because an error may occur on the connection during this process, a NULL return value from mysql_fetch_row() does not necessarily mean the end of the result set was reached normally. In this case, you can use mysql_eof() to determine what happened. mysql_eof() returns a nonzero value if the end of the result set was reached and zero if an error occurred.

Historically, mysql_eof() predates the standard MySQL error functions mysql_errno() and mysql_error() . Because those error functions provide the same information, their use is preferred over mysql_eof() , which is deprecated. (In fact, they provide more information, because mysql_eof() returns only a boolean value whereas the error functions indicate a reason for the error when one occurs.)

Return Values

Zero for success. Nonzero if the end of the result set has been reached.

Errors

None.

Example

The following example shows how you might use mysql_eof() :

mysql_query(&mysql,"SELECT * FROM some_table");
result = mysql_use_result(&mysql);
while((row = mysql_fetch_row(result)))
{
    // do something with data
}
if(!mysql_eof(result))  // mysql_fetch_row() failed due to an error
{
    fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
}
                                

However, you can achieve the same effect with the standard MySQL error functions:

mysql_query(&mysql,"SELECT * FROM some_table");
result = mysql_use_result(&mysql);
while((row = mysql_fetch_row(result)))
{
    // do something with data
}
if(mysql_errno(&mysql))  // mysql_fetch_row() failed due to an error
{
    fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
}
                                

28.7.7.14 mysql_errno()

unsigned int mysql_errno(MYSQL *mysql)

描述

For the connection specified by mysql , mysql_errno() returns the error code for the most recently invoked API function that can succeed or fail. A return value of zero means that no error occurred. Client error message numbers are listed in the MySQL errmsg.h header file. Server error message numbers are listed in mysqld_error.h . Errors also are listed at Appendix B, Errors, Error Codes, and Common Problems .

Note

Some functions such as mysql_fetch_row() do not set mysql_errno() if they succeed. A rule of thumb is that all functions that have to ask the server for information reset mysql_errno() if they succeed.

MySQL-specific error numbers returned by mysql_errno() differ from SQLSTATE values returned by mysql_sqlstate() . For example, the mysql client program displays errors using the following format, where 1146 is the mysql_errno() value and '42S02' is the corresponding mysql_sqlstate() value:

shell> SELECT * FROM no_such_table;
ERROR 1146 (42S02): Table 'test.no_such_table' doesn't exist
                                
Return Values

An error code value for the last mysql_ xxx () call, if it failed. zero means no error occurred.

Errors

None.

28.7.7.15 mysql_error()

const char *mysql_error(MYSQL *mysql)

描述

For the connection specified by mysql , mysql_error() returns a null-terminated string containing the error message for the most recently invoked API function that failed. If a function did not fail, the return value of mysql_error() may be the previous error or an empty string to indicate no error.

A rule of thumb is that all functions that have to ask the server for information reset mysql_error() if they succeed.

For functions that reset mysql_error() , either of these two tests can be used to check for an error:

if(*mysql_error(&mysql))
{
  // an error occurred
}
if(mysql_error(&mysql)[0])
{
  // an error occurred
}
                                

The language of the client error messages may be changed by recompiling the MySQL client library. You can choose error messages in several different languages. See Section 10.11, “Setting the Error Message Language” .

Return Values

A null-terminated character string that describes the error. An empty string if no error occurred.

Errors

None.

28.7.7.16 mysql_escape_string()

Note

Do not use this function. mysql_escape_string() does not have arguments that enable it to respect the current character set or the quoting context. Use mysql_real_escape_string_quote() instead.

28.7.7.17 mysql_fetch_field()

MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result)

描述

Returns the definition of one column of a result set as a MYSQL_FIELD structure. Call this function repeatedly to retrieve information about all columns in the result set. mysql_fetch_field() returns NULL when no more fields are left.

For metadata-optional connections, this function returns NULL when the resultset_metadata system variable is set to NONE . To check whether a result set has metadata, use the mysql_result_metadata() function. For details about managing result set metadata transfer, see Section 28.7.23, “C API Optional Result Set Metadata” .

mysql_fetch_field() is reset to return information about the first field each time you execute a new SELECT query. The field returned by mysql_fetch_field() is also affected by calls to mysql_field_seek() .

If you've called mysql_query() to perform a SELECT on a table but have not called mysql_store_result() , MySQL returns the default blob length (8KB) if you call mysql_fetch_field() to ask for the length of a BLOB field. (The 8KB size is chosen because MySQL does not know the maximum length for the BLOB . This should be made configurable sometime.) Once you've retrieved the result set, field->max_length contains the length of the largest value for this column in the specific query.

Return Values

The MYSQL_FIELD structure for the current column. NULL if no columns are left or if the result set has no metadata.

Errors

None.

Example
MYSQL_FIELD *field;
while((field = mysql_fetch_field(result)))
{
    printf("field name %s\n", field->name);
}
                                

28.7.7.18 mysql_fetch_field_direct()

MYSQL_FIELD *mysql_fetch_field_direct(MYSQL_RES *result, unsigned int fieldnr)

描述

Given a field number fieldnr for a column within a result set, returns that column's field definition as a MYSQL_FIELD structure. Use this function to retrieve the definition for an arbitrary column. Specify a value for fieldnr in the range from 0 to mysql_num_fields(result)-1 .

For metadata-optional connections, this function returns NULL when the resultset_metadata system variable is set to NONE . To check whether a result set has metadata, use the mysql_result_metadata() function. For details about managing result set metadata transfer, see Section 28.7.23, “C API Optional Result Set Metadata” .

Return Values

The MYSQL_FIELD structure for the specified column. NULL if the result set has no metadata.

Errors

None.

Example
unsigned int num_fields;
unsigned int i;
MYSQL_FIELD *field;
num_fields = mysql_num_fields(result);
for(i = 0; i < num_fields; i++)
{
    field = mysql_fetch_field_direct(result, i);
    printf("Field %u is %s\n", i, field->name);
}
                                

28.7.7.19 mysql_fetch_fields()

MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *result)

描述

Returns an array of all MYSQL_FIELD structures for a result set. Each structure provides the field definition for one column of the result set.

For metadata-optional connections, this function returns NULL when the resultset_metadata system variable is set to NONE . To check whether a result set has metadata, use the mysql_result_metadata() function. For details about managing result set metadata transfer, see Section 28.7.23, “C API Optional Result Set Metadata” .

Return Values

An array of MYSQL_FIELD structures for all columns of a result set. NULL if the result set has no metadata.

Errors

None.

Example
unsigned int num_fields;
unsigned int i;
MYSQL_FIELD *fields;
num_fields = mysql_num_fields(result);
fields = mysql_fetch_fields(result);
for(i = 0; i < num_fields; i++)
{
   printf("Field %u is %s\n", i, fields[i].name);
}
                                

28.7.7.20 mysql_fetch_lengths()

unsigned long *mysql_fetch_lengths(MYSQL_RES *result)

描述

Returns the lengths of the columns of the current row within a result set. If you plan to copy field values, this length information is also useful for optimization, because you can avoid calling strlen() . In addition, if the result set contains binary data, you must use this function to determine the size of the data, because strlen() returns incorrect results for any field containing null characters.

The length for empty columns and for columns containing NULL values is zero. To see how to distinguish these two cases, see the description for mysql_fetch_row() .

Return Values

An array of unsigned long integers representing the size of each column (not including any terminating null bytes). NULL if an error occurred.

Errors

mysql_fetch_lengths() is valid only for the current row of the result set. It returns NULL if you call it before calling mysql_fetch_row() or after retrieving all rows in the result.

Example
MYSQL_ROW row;
unsigned long *lengths;
unsigned int num_fields;
unsigned int i;
row = mysql_fetch_row(result);
if (row)
{
    num_fields = mysql_num_fields(result);
    lengths = mysql_fetch_lengths(result);
    for(i = 0; i < num_fields; i++)
    {
         printf("Column %u is %lu bytes in length.\n",
                i, lengths[i]);
    }
}
                                

28.7.7.21 mysql_fetch_row()

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)

描述

Retrieves the next row of a result set. When used after mysql_store_result() , mysql_fetch_row() returns NULL when there are no more rows to retrieve. When used after mysql_use_result() , mysql_fetch_row() returns NULL when there are no more rows to retrieve or if an error occurred.

The number of values in the row is given by mysql_num_fields(result) . If row holds the return value from a call to mysql_fetch_row() , pointers to the values are accessed as row[0] to row[mysql_num_fields(result)-1] . NULL values in the row are indicated by NULL pointers.

The lengths of the field values in the row may be obtained by calling mysql_fetch_lengths() . Empty fields and fields containing NULL both have length 0; you can distinguish these by checking the pointer for the field value. If the pointer is NULL , the field is NULL ; otherwise, the field is empty.

Return Values

A MYSQL_ROW structure for the next row. NULL if there are no more rows to retrieve or if an error occurred.

Errors

Errors are not reset between calls to mysql_fetch_row()

Example
MYSQL_ROW row;
unsigned int num_fields;
unsigned int i;
num_fields = mysql_num_fields(result);
while ((row = mysql_fetch_row(result)))
{
   unsigned long *lengths;
   lengths = mysql_fetch_lengths(result);
   for(i = 0; i < num_fields; i++)
   {
       printf("[%.*s] ", (int) lengths[i],
              row[i] ? row[i] : "NULL");
   }
   printf("\n");
}
                                

28.7.7.22 mysql_field_count()

unsigned int mysql_field_count(MYSQL *mysql)

描述

Returns the number of columns for the most recent query on the connection.

The normal use of this function is when mysql_store_result() returned NULL (and thus you have no result set pointer). In this case, you can call mysql_field_count() to determine whether mysql_store_result() should have produced a nonempty result. This enables the client program to take proper action without knowing whether the query was a SELECT (or SELECT -like) statement. The example shown here illustrates how this may be done.

See Section 28.7.25.1, “Why mysql_store_result() Sometimes Returns NULL After mysql_query() Returns Success” .

Return Values

An unsigned integer representing the number of columns in a result set.

Errors

None.

Example
MYSQL_RES *result;
unsigned int num_fields;
unsigned int num_rows;
if (mysql_query(&mysql,query_string))
{
    // error
}
else // query succeeded, process any data returned by it
{
    result = mysql_store_result(&mysql);
    if (result)  // there are rows
    {
        num_fields = mysql_num_fields(result);
        // retrieve rows, then call mysql_free_result(result)
    }
    else  // mysql_store_result() returned nothing; should it have?
    {
        if(mysql_field_count(&mysql) == 0)
        {
            // query does not return data
            // (it was not a SELECT)
            num_rows = mysql_affected_rows(&mysql);
        }
        else // mysql_store_result() should have returned data
        {
            fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
        }
    }
}
                                

An alternative is to replace the mysql_field_count(&mysql) call with mysql_errno(&mysql) . In this case, you are checking directly for an error from mysql_store_result() rather than inferring from the value of mysql_field_count() whether the statement was a SELECT .

28.7.7.23 mysql_field_seek()

MYSQL_FIELD_OFFSET mysql_field_seek(MYSQL_RES *result, MYSQL_FIELD_OFFSET offset)

描述

Sets the field cursor to the given offset. The next call to mysql_fetch_field() retrieves the field definition of the column associated with that offset.

To seek to the beginning of a row, pass an offset value of zero.

Return Values

The previous value of the field cursor.

Errors

None.

28.7.7.24 mysql_field_tell()

MYSQL_FIELD_OFFSET mysql_field_tell(MYSQL_RES *result)

描述

Returns the position of the field cursor used for the last mysql_fetch_field() . This value can be used as an argument to mysql_field_seek() .

Return Values

The current offset of the field cursor.

Errors

None.

28.7.7.25 mysql_free_result()

void mysql_free_result(MYSQL_RES *result)

描述

Frees the memory allocated for a result set by mysql_store_result() , mysql_use_result() , mysql_list_dbs() , and so forth. When you are done with a result set, you must free the memory it uses by calling mysql_free_result() .

Do not attempt to access a result set after freeing it.

Return Values

None.

Errors

None.

28.7.7.26 mysql_get_character_set_info()

void mysql_get_character_set_info(MYSQL *mysql, MY_CHARSET_INFO *cs)

描述

This function provides information about the default client character set. The default character set may be changed with the mysql_set_character_set() function.

Example

This example shows the fields that are available in the MY_CHARSET_INFO structure:

if (!mysql_set_character_set(&mysql, "utf8"))
{
    MY_CHARSET_INFO cs;
    mysql_get_character_set_info(&mysql, &cs);
    printf("character set information:\n");
    printf("character set+collation number: %d\n", cs.number);
    printf("character set name: %s\n", cs.name);
    printf("collation name: %s\n", cs.csname);
    printf("comment: %s\n", cs.comment);
    printf("directory: %s\n", cs.dir);
    printf("multi byte character min. length: %d\n", cs.mbminlen);
    printf("multi byte character max. length: %d\n", cs.mbmaxlen);
}
                                

28.7.7.27 mysql_get_client_info()

const char *mysql_get_client_info(void)

描述

Returns a string that represents the MySQL client library version; for example, "8.0.17" .

The function value is the version of MySQL or Connector/C that provides the client library. For more information, see Section 28.7.4.5, “C API Server and Client Library Versions” .

Return Values

A character string that represents the MySQL client library version.

Errors

None.

28.7.7.28 mysql_get_client_version()

unsigned long mysql_get_client_version(void)

描述

Returns an integer that represents the MySQL client library version. The value has the format XYYZZ where X is the major version, YY is the release level (or minor version), and ZZ is the sub-version within the release level:

major_version*10000 + release_level*100 + sub_version
                                

For example, "8.0.17" is returned as 80017 .

The function value is the version of MySQL or Connector/C that provides the client library. For more information, see Section 28.7.4.5, “C API Server and Client Library Versions” .

Return Values

An integer that represents the MySQL client library version.

Errors

None.

28.7.7.29 mysql_get_host_info()

const char *mysql_get_host_info(MYSQL *mysql)

描述

Returns a string describing the type of connection in use, including the server host name.

Return Values

A character string representing the server host name and the connection type.

Errors

None.

28.7.7.30 mysql_get_option()

int mysql_get_option(MYSQL *mysql, enum mysql_option option, const void *arg)

描述

Returns the current value of an option settable using mysql_options() . The value should be treated as read only.

The option argument is the option for which you want its value. The arg argument is a pointer to a variable in which to store the option value. arg must be a pointer to a variable of the type appropriate for the option argument. The following table shows which variable type to use for each option value.

arg Type Applicable option Values
unsigned int MYSQL_OPT_CONNECT_TIMEOUT , MYSQL_OPT_PROTOCOL , MYSQL_OPT_READ_TIMEOUT , MYSQL_OPT_RETRY_COUNT , MYSQL_OPT_SSL_FIPS_MODE , MYSQL_OPT_SSL_MODE , MYSQL_OPT_WRITE_TIMEOUT
unsigned long MYSQL_OPT_MAX_ALLOWED_PACKET , MYSQL_OPT_NET_BUFFER_LENGTH
bool MYSQL_ENABLE_CLEARTEXT_PLUGIN , MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS , MYSQL_OPT_GET_SERVER_PUBLIC_KEY , MYSQL_OPT_LOCAL_INFILE , MYSQL_OPT_OPTIONAL_RESULTSET_METADATA , MYSQL_OPT_RECONNECT , MYSQL_REPORT_DATA_TRUNCATION
const char * MYSQL_DEFAULT_AUTH , MYSQL_OPT_BIND , MYSQL_OPT_SSL_CA , MYSQL_OPT_SSL_CAPATH , MYSQL_OPT_SSL_CERT , MYSQL_OPT_SSL_CIPHER , MYSQL_OPT_SSL_CRL , MYSQL_OPT_SSL_CRLPATH , MYSQL_OPT_SSL_KEY , MYSQL_OPT_TLS_VERSION , MYSQL_PLUGIN_DIR , MYSQL_READ_DEFAULT_FILE , MYSQL_READ_DEFAULT_GROUP , MYSQL_SERVER_PUBLIC_KEY , MYSQL_SET_CHARSET_DIR , MYSQL_SET_CHARSET_NAME , MYSQL_SHARED_MEMORY_BASE_NAME
argument not used MYSQL_OPT_COMPRESS
cannot be queried (error is returned) MYSQL_INIT_COMMAND , MYSQL_OPT_CONNECT_ATTR_DELETE , MYSQL_OPT_CONNECT_ATTR_RESET , MYSQL_OPT_NAMED_PIPE
Return Values

Zero for success. Nonzero if an error occurred; this occurs for option values that cannot be queried.

Example

The following call tests the MYSQL_OPT_RECONNECT option. After the call returns successfully, the value of reconnect is true or false to indicate whether automatic reconnection is enabled.

bool reconnect;
if (mysql_get_option(mysql, MYSQL_OPT_RECONNECT, &reconnect))
  fprintf(stderr, "mysql_get_options() failed\n");
                                

28.7.7.31 mysql_get_proto_info()

unsigned int mysql_get_proto_info(MYSQL *mysql)

描述

Returns the protocol version used by current connection.

Return Values

An unsigned integer representing the protocol version used by the current connection.

Errors

None.

28.7.7.32 mysql_get_server_info()

const char *mysql_get_server_info(MYSQL *mysql)

描述

Returns a string that represents the MySQL server version; for example, "8.0.17" .

Return Values

A character string that represents the MySQL server version.

Errors

None.

28.7.7.33 mysql_get_server_version()

unsigned long mysql_get_server_version(MYSQL *mysql)

描述

Returns an integer that represents the MySQL server version. The value has the format XYYZZ where X is the major version, YY is the release level (or minor version), and ZZ is the sub-version within the release level:

major_version*10000 + release_level*100 + sub_version
                                

For example, "8.0.17" is returned as 80017 .

This function is useful in client programs for determining whether some version-specific server capability exists.

Return Values

An integer that represents the MySQL server version.

Errors

None.

28.7.7.34 mysql_get_ssl_cipher()

const char *mysql_get_ssl_cipher(MYSQL *mysql)

描述

mysql_get_ssl_cipher() returns the encryption cipher used for the given connection to the server. mysql is the connection handler returned from mysql_init() .

Return Values

A string naming the encryption cipher used for the connection, or NULL if the connection is not encrypted.

28.7.7.35 mysql_hex_string()

unsigned long mysql_hex_string(char *to, const char *from, unsigned long length)

描述

This function creates a legal SQL string for use in an SQL statement. See Section 9.1.1, “String Literals” .

The string in the from argument is encoded in hexadecimal format, with each character encoded as two hexadecimal digits. The result is placed in the to argument, followed by a terminating null byte.

The string pointed to by from must be length bytes long. You must allocate the to buffer to be at least length*2+1 bytes long. When mysql_hex_string() returns, the contents of to is a null-terminated string. The return value is the length of the encoded string, not including the terminating null byte.

The return value can be placed into an SQL statement using either X' value ' or 0x value format. However, the return value does not include the X'...' or 0x . The caller must supply whichever of those is desired.

Example
char query[1000],*end;
end = strmov(query,"INSERT INTO test_table values(");
end = strmov(end,"X'");
end += mysql_hex_string(end,"What is this",12);
end = strmov(end,"',X'");
end += mysql_hex_string(end,"binary data: \0\r\n",16);
end = strmov(end,"')");
if (mysql_real_query(&mysql,query,(unsigned int) (end - query)))
{
   fprintf(stderr, "Failed to insert row, Error: %s\n",
           mysql_error(&mysql));
}
                                

The strmov() function used in the example is included in the libmysqlclient library and works like strcpy() but returns a pointer to the terminating null of the first parameter.

Return Values

The length of the encoded string that is placed into to , not including the terminating null character.

Errors

None.

28.7.7.36 mysql_info()

const char *mysql_info(MYSQL *mysql)

描述

Retrieves a string providing information about the most recently executed statement, but only for the statements listed here. For other statements, mysql_info() returns NULL . The format of the string varies depending on the type of statement, as described here. The numbers are illustrative only; the string contains values appropriate for the statement.

  • INSERT INTO ... SELECT ...

    String format: Records: 100 Duplicates: 0 Warnings: 0

  • INSERT INTO ... VALUES (...),(...),(...)...

    String format: Records: 3 Duplicates: 0 Warnings: 0

  • LOAD DATA

    String format: Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

  • ALTER TABLE

    String format: Records: 3 Duplicates: 0 Warnings: 0

  • UPDATE

    String format: Rows matched: 40 Changed: 40 Warnings: 0

mysql_info() returns a non- NULL value for INSERT ... VALUES only for the multiple-row form of the statement (that is, only if multiple value lists are specified).

Return Values

A character string representing additional information about the most recently executed statement. NULL if no information is available for the statement.

Errors

None.

28.7.7.37 mysql_init()

MYSQL *mysql_init(MYSQL *mysql)

描述

Allocates or initializes a MYSQL object suitable for mysql_real_connect() . If mysql is a NULL pointer, the function allocates, initializes, and returns a new object. Otherwise, the object is initialized and the address of the object is returned. If mysql_init() allocates a new object, it is freed when mysql_close() is called to close the connection.

In a nonmultithreaded environment, mysql_init() invokes mysql_library_init() automatically as necessary. However, mysql_library_init() is not thread-safe in a multithreaded environment, and thus neither is mysql_init() . Before calling mysql_init() , either call mysql_library_init() prior to spawning any threads, or use a mutex to protect the mysql_library_init() call. This should be done prior to any other client library call.

Return Values

An initialized MYSQL* handler. NULL if there was insufficient memory to allocate a new object.

Errors

In case of insufficient memory, NULL is returned.

28.7.7.38 mysql_insert_id()

my_ulonglong mysql_insert_id(MYSQL *mysql)

描述

Returns the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE statement. Use this function after you have performed an INSERT statement into a table that contains an AUTO_INCREMENT field, or have used INSERT or UPDATE to set a column value with LAST_INSERT_ID( expr ) .

The return value of mysql_insert_id() is always zero unless explicitly updated under one of the following conditions:

The return value of mysql_insert_id() can be simplified to the following sequence:

  1. If there is an AUTO_INCREMENT column, and an automatically generated value was successfully inserted, return the first such value.

  2. If LAST_INSERT_ID( expr ) occurred in the statement, return expr , even if there was an AUTO_INCREMENT column in the affected table.

  3. The return value varies depending on the statement used. When called after an INSERT statement:

    • If there is an AUTO_INCREMENT column in the table, and there were some explicit values for this column that were successfully inserted into the table, return the last of the explicit values.

    When called after an INSERT ... ON DUPLICATE KEY UPDATE statement:

    • If there is an AUTO_INCREMENT column in the table and there were some explicit successfully inserted values or some updated values, return the last of the inserted or updated values.

mysql_insert_id() returns 0 if the previous statement does not use an AUTO_INCREMENT value. If you need to save the value for later, be sure to call mysql_insert_id() immediately after the statement that generates the value.

The value of mysql_insert_id() is affected only by statements issued within the current client connection. It is not affected by statements issued by other clients.

The LAST_INSERT_ID() SQL function will contain the value of the first automatically generated value that was successfully inserted. LAST_INSERT_ID() is not reset between statements because the value of that function is maintained in the server. Another difference from mysql_insert_id() is that LAST_INSERT_ID() is not updated if you set an AUTO_INCREMENT column to a specific nonspecial value. See Section 12.15, “Information Functions” .

mysql_insert_id() returns 0 following a CALL statement for a stored procedure that generates an AUTO_INCREMENT value because in this case mysql_insert_id() applies to CALL and not the statement within the procedure. Within the procedure, you can use LAST_INSERT_ID() at the SQL level to obtain the AUTO_INCREMENT value.

The reason for the differences between LAST_INSERT_ID() and mysql_insert_id() is that LAST_INSERT_ID() is made easy to use in scripts while mysql_insert_id() tries to provide more exact information about what happens to the AUTO_INCREMENT column.

Note

The OK packet used in the client/server protocol holds information such as is used for session state tracking. When clients read the OK packet to know whether there is a session state change, this resets values such as the last insert ID and the number of affected rows. Such changes cause mysql_insert_id() to return 0 after execution of commands including but not necessarily limited to COM_PING , COM_REFRESH , and COM_INIT_DB .

Return Values

Described in the preceding discussion.

Errors
  • ER_AUTO_INCREMENT_CONFLICT

    A user-specified AUTO_INCREMENT value in a multi INSERT statement falls within the range between the current AUTO_INCREMENT value and the sum of the current and number of rows affected values.

28.7.7.39 mysql_kill()

int mysql_kill(MYSQL *mysql, unsigned long pid)

描述
Note

mysql_kill() is deprecated and will be removed in a future version of MySQL. Instead, use mysql_query() to execute a KILL statement.

Asks the server to kill the thread specified by pid .

This function is deprecated. Use mysql_query() to issue an SQL KILL statement instead.

mysql_kill() cannot handle values larger than 32 bits, but to guard against killing the wrong thread returns an error in these cases:

Return Values

Zero for success. Nonzero if an error occurred.

Errors

28.7.7.40 mysql_library_end()

void mysql_library_end(void)

描述

This function finalizes the MySQL client library. Call it when you are done using the library (for example, after disconnecting from the server).

Note

To avoid memory leaks after the application is done using the library (for example, after closing the connection to the server), be sure to call mysql_library_end() explicitly. This enables memory managment to be performed to clean up and free resources used by the library.

For usage information, see Section 28.7.6, “C API Function Overview” , and Section 28.7.7.41, “mysql_library_init()” .

28.7.7.41 mysql_library_init()

int mysql_library_init(int argc, char **argv, char **groups)

描述

Call this function to initialize the MySQL client library before you call any other MySQL function.

Note

To avoid memory leaks after the application is done using the library (for example, after closing the connection to the server), be sure to call mysql_library_end() explicitly. This enables memory managment to be performed to clean up and free resources used by the library. See Section 28.7.7.40, “mysql_library_end()” .

In a nonmultithreaded environment, the call to mysql_library_init() may be omitted, because mysql_init() will invoke it automatically as necessary. However, mysql_library_init() is not thread-safe in a multithreaded environment, and thus neither is mysql_init() , which calls mysql_library_init() . You must either call mysql_library_init() prior to spawning any threads, or else use a mutex to protect the call, whether you invoke mysql_library_init() or indirectly through mysql_init() . Do this prior to any other client library call.

The argc , argv , and groups arguments are unused. In older MySQL versions, they were used for applications linked against the embedded server, which is no longer supported. The call now should be written as mysql_library_init(0, NULL, NULL) .

#include <mysql.h>
#include <stdlib.h>
int main(void) {
  if (mysql_library_init(0, NULL, NULL)) {
    fprintf(stderr, "could not initialize MySQL client library\n");
    exit(1);
  }
  /* Use any MySQL API functions here */
  mysql_library_end();
  return EXIT_SUCCESS;
}
                                
Return Values

Zero for success. Nonzero if an error occurred.

28.7.7.42 mysql_list_dbs()

MYSQL_RES *mysql_list_dbs(MYSQL *mysql, const char *wild)

描述

Returns a result set consisting of database names on the server that match the simple regular expression specified by the wild parameter. wild may contain the wildcard characters % or _ , or may be a NULL pointer to match all databases. Calling mysql_list_dbs() is similar to executing the query SHOW DATABASES [LIKE wild ] .

You must free the result set with mysql_free_result() .

Return Values

A MYSQL_RES result set for success. NULL if an error occurred.

Errors

28.7.7.43 mysql_list_fields()

MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table, const char *wild)

描述
Note

mysql_list_fields() is deprecated and will be removed in a future version of MySQL. Instead, use mysql_query() to execute a SHOW COLUMNS statement.

Returns an empty result set for which the metadata provides information about the columns in the given table that match the simple regular expression specified by the wild parameter. wild may contain the wildcard characters % or _ , or may be a NULL pointer to match all fields. Calling mysql_list_fields() is similar to executing the query SHOW COLUMNS FROM tbl_name [LIKE wild ] .

It is preferable to use SHOW COLUMNS FROM tbl_name instead of mysql_list_fields() .

You must free the result set with mysql_free_result() .

Return Values

A MYSQL_RES result set for success. NULL if an error occurred.

Errors
Example
int i;
MYSQL_RES *tbl_cols = mysql_list_fields(mysql, "mytbl", "f%");
unsigned int field_cnt = mysql_num_fields(tbl_cols);
printf("Number of columns: %d\n", field_cnt);
for (i=0; i < field_cnt; ++i)
{
  /* col describes i-th column of the table */
  MYSQL_FIELD *col = mysql_fetch_field_direct(tbl_cols, i);
  printf ("Column %d: %s\n", i, col->name);
}
mysql_free_result(tbl_cols);
                                

28.7.7.44 mysql_list_processes()

MYSQL_RES *mysql_list_processes(MYSQL *mysql)

描述
Note

mysql_list_processes() is deprecated and will be removed in a future version of MySQL. Instead, use mysql_query() to execute a SHOW PROCESSLIST statement.

Returns a result set describing the current server threads. This is the same kind of information as that reported by mysqladmin processlist or a SHOW PROCESSLIST query.

You must free the result set with mysql_free_result() .

Return Values

A MYSQL_RES result set for success. NULL if an error occurred.

Errors

28.7.7.45 mysql_list_tables()

MYSQL_RES *mysql_list_tables(MYSQL *mysql, const char *wild)

描述

Returns a result set consisting of table names in the current database that match the simple regular expression specified by the wild parameter. wild may contain the wildcard characters % or _ , or may be a NULL pointer to match all tables. Calling mysql_list_tables() is similar to executing the query SHOW TABLES [LIKE wild ] .

You must free the result set with mysql_free_result() .

Return Values

A MYSQL_RES result set for success. NULL if an error occurred.

Errors

28.7.7.46 mysql_more_results()

bool mysql_more_results(MYSQL *mysql)

描述

This function is used when you execute multiple statements specified as a single statement string, or when you execute CALL statements, which can return multiple result sets.

mysql_more_results() true if more results exist from the currently executed statement, in which case the application must call mysql_next_result() to fetch the results.

Return Values

TRUE (1) if more results exist. FALSE (0) if no more results exist.

In most cases, you can call mysql_next_result() instead to test whether more results exist and initiate retrieval if so.

See Section 28.7.19, “C API Multiple Statement Execution Support” , and Section 28.7.7.47, “mysql_next_result()” .

Errors

None.

28.7.7.47 mysql_next_result()

int mysql_next_result(MYSQL *mysql)

描述

This function is used when you execute multiple statements specified as a single statement string, or when you use CALL statements to execute stored procedures, which can return multiple result sets.

mysql_next_result() reads the next statement result and returns a status to indicate whether more results exist. If mysql_next_result() returns an error, there are no more results.

Before each call to mysql_next_result() , you must call mysql_free_result() for the current statement if it is a statement that returned a result set (rather than just a result status).

After calling mysql_next_result() the state of the connection is as if you had called mysql_real_query() or mysql_query() for the next statement. This means that you can call mysql_store_result() , mysql_warning_count() , mysql_affected_rows() , and so forth.

If your program uses CALL statements to execute stored procedures, the CLIENT_MULTI_RESULTS flag must be enabled. This is because each CALL returns a result to indicate the call status, in addition to any result sets that might be returned by statements executed within the procedure. Because CALL can return multiple results, process them using a loop that calls mysql_next_result() to determine whether there are more results.

CLIENT_MULTI_RESULTS can be enabled when you call mysql_real_connect() , either explicitly by passing the CLIENT_MULTI_RESULTS flag itself, or implicitly by passing CLIENT_MULTI_STATEMENTS (which also enables CLIENT_MULTI_RESULTS ). CLIENT_MULTI_RESULTS is enabled by default.

It is also possible to test whether there are more results by calling mysql_more_results() . However, this function does not change the connection state, so if it returns true, you must still call mysql_next_result() to advance to the next result.

For an example that shows how to use mysql_next_result() , see Section 28.7.19, “C API Multiple Statement Execution Support” .

Return Values
Return Value 描述
0 Successful and there are more results
-1 Successful and there are no more results
>0 An error occurred
Errors

28.7.7.48 mysql_num_fields()

unsigned int mysql_num_fields(MYSQL_RES *result)

To pass a MYSQL* argument instead, use unsigned int mysql_field_count(MYSQL *mysql) .

描述

Returns the number of columns in a result set.

You can get the number of columns either from a pointer to a result set or to a connection handler. You would use the connection handler if mysql_store_result() or mysql_use_result() returned NULL (and thus you have no result set pointer). In this case, you can call mysql_field_count() to determine whether mysql_store_result() should have produced a nonempty result. This enables the client program to take proper action without knowing whether the query was a SELECT (or SELECT -like) statement. The example shown here illustrates how this may be done.

See Section 28.7.25.1, “Why mysql_store_result() Sometimes Returns NULL After mysql_query() Returns Success” .

Return Values

An unsigned integer representing the number of columns in a result set.

Errors

None.

Example
MYSQL_RES *result;
unsigned int num_fields;
unsigned int num_rows;
if (mysql_query(&mysql,query_string))
{
    // error
}
else // query succeeded, process any data returned by it
{
    result = mysql_store_result(&mysql);
    if (result)  // there are rows
    {
        num_fields = mysql_num_fields(result);
        // retrieve rows, then call mysql_free_result(result)
    }
    else  // mysql_store_result() returned nothing; should it have?
    {
        if (mysql_errno(&mysql))
        {
           fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
        }
        else if (mysql_field_count(&mysql) == 0)
        {
            // query does not return data
            // (it was not a SELECT)
            num_rows = mysql_affected_rows(&mysql);
        }
    }
}
                                

An alternative (if you know that your query should have returned a result set) is to replace the mysql_errno(&mysql) call with a check whether mysql_field_count(&mysql) returns 0. This happens only if something went wrong.

28.7.7.49 mysql_num_rows()

my_ulonglong mysql_num_rows(MYSQL_RES *result)

描述

Returns the number of rows in the result set.

The use of mysql_num_rows() depends on whether you use mysql_store_result() or mysql_use_result() to return the result set. If you use mysql_store_result() , mysql_num_rows() may be called immediately. If you use mysql_use_result() , mysql_num_rows() does not return the correct value until all the rows in the result set have been retrieved.

mysql_num_rows() is intended for use with statements that return a result set, such as SELECT . For statements such as INSERT , UPDATE , or DELETE , the number of affected rows can be obtained with mysql_affected_rows() .

Return Values

The number of rows in the result set.

Errors

None.

28.7.7.50 mysql_options()

int mysql_options(MYSQL *mysql, enum mysql_option option, const void *arg)

描述

Can be used to set extra connect options and affect behavior for a connection. This function may be called multiple times to set several options. To retrieve option values, use mysql_get_option() .

Call mysql_options() after mysql_init() and before mysql_connect() or mysql_real_connect() .

The option argument is the option that you want to set; the arg argument is the value for the option. If the option is an integer, specify a pointer to the value of the integer as the arg argument.

Options for information such as SSL certificate and key files are used to establish an encrypted connection if such connections are available, but do not enforce any requirement that the connection obtained be encrypted. To require an encrypted connection, use the technique described in Section 28.7.18, “C API Encrypted Connection Support” .

The following list describes the possible options, their effect, and how arg is used for each option. For option descriptions that indicate arg is unused, its value is irrelevant; it is conventional to pass 0.

  • MYSQL_DEFAULT_AUTH (argument type: char * )

    The name of the authentication plugin to use.

  • MYSQL_ENABLE_CLEARTEXT_PLUGIN (argument type: bool * )

    Enable the mysql_clear_password cleartext authentication plugin. See Section 6.5.1.4, “Client-Side Cleartext Pluggable Authentication” .

  • MYSQL_INIT_COMMAND (argument type: char * )

    SQL statement to execute when connecting to the MySQL server. Automatically re-executed if reconnection occurs.

  • MYSQL_OPT_BIND (argument: char * )

    The network interface from which to connect to the server. This is used when the client host has multiple network interfaces. The argument is a host name or IP address (specified as a string).

  • MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS (argument type: bool * )

    Indicate whether the client can handle expired passwords. See Section 6.3.9, “Server Handling of Expired Passwords” .

  • MYSQL_OPT_COMPRESS (argument: not used)

    Use the compressed client/server protocol.

  • MYSQL_OPT_CONNECT_ATTR_DELETE (argument type: char * )

    Given a key name, this option deletes a key-value pair from the current set of connection attributes to pass to the server at connect time. The argument is a pointer to a null-terminated string naming the key. Comparison of the key name with existing keys is case-sensitive.

    See also the description for the MYSQL_OPT_CONNECT_ATTR_RESET option, as well as the description for the MYSQL_OPT_CONNECT_ATTR_ADD option in the description of the mysql_options4() function. That function description also includes a usage example.

    The Performance Schema exposes connection attributes through the session_connect_attrs and session_account_connect_attrs tables. See Section 26.12.9, “Performance Schema Connection Attribute Tables” .

  • MYSQL_OPT_CONNECT_ATTR_RESET (argument not used)

    This option resets (clears) the current set of connection attributes to pass to the server at connect time.

    See also the description for the MYSQL_OPT_CONNECT_ATTR_DELETE option, as well as the description for the MYSQL_OPT_CONNECT_ATTR_ADD option in the description of the mysql_options4() function. That function description also includes a usage example.

    The Performance Schema exposes connection attributes through the session_connect_attrs and session_account_connect_attrs tables. See Section 26.12.9, “Performance Schema Connection Attribute Tables” .

  • MYSQL_OPT_CONNECT_TIMEOUT (argument type: unsigned int * )

    The connect timeout in seconds.

  • MYSQL_OPT_GET_SERVER_PUBLIC_KEY (argument type: bool * )

    Enables the client to request from the server the public key required for RSA key pair-based password exchange. This option applies to clients that authenticate with the caching_sha2_password authentication plugin. For that plugin, the server does not send the public key unless requested. This option is ignored for accounts that do not authenticate with that plugin. It is also ignored if RSA-based password exchange is not used, as is the case when the client connects to the server using a secure connection.

    If MYSQL_SERVER_PUBLIC_KEY is given and specifies a valid public key file, it takes precedence over MYSQL_OPT_GET_SERVER_PUBLIC_KEY .

    For information about the caching_sha2_password plugin, see Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication” .

  • MYSQL_OPT_LOCAL_INFILE (argument type: optional pointer to unsigned int )

    This option affects client-side LOCAL capability for LOAD DATA operations. By default, LOCAL capability is determined by the default compiled into the MySQL client library (see Section 13.2.7, “LOAD DATA Syntax” ). To control this capability explicitly, invoke mysql_options() to set the MYSQL_OPT_LOCAL_INFILE option:

    • LOCAL is disabled if the pointer points to an unsigned int that has a zero value.

    • LOCAL is enabled if no pointer is given or if the pointer points to an unsigned int that has a nonzero value.

    Successful use of a LOCAL load operation by a client also requires that the server permits it.

  • MYSQL_OPT_MAX_ALLOWED_PACKET (argument: unsigned long * )

    This option sets the max_allowed_packet system variable. If the mysql argument is non- NULL , the call sets the session system variable value for that session. If mysql is NULL , the call sets the global system variable value.

  • MYSQL_OPT_NAMED_PIPE (argument: not used)

    Use a named pipe to connect to the MySQL server on Windows, if the server permits named-pipe connections.

  • MYSQL_OPT_NET_BUFFER_LENGTH (argument: unsigned long * )

    This option sets the net_buffer_length system variable. If the mysql argument is non- NULL , the call sets the session system variable value for that session. If mysql is NULL , the call sets the global system variable value.

  • MYSQL_OPT_OPTIONAL_RESULTSET_METADATA (argument type: bool * )

    This flag makes result set metadata optional. It is an alternative way of setting the CLIENT_OPTIONAL_RESULTSET_METADATA connection flag for the mysql_real_connect() function. For details about managing result set metadata transfer, see Section 28.7.23, “C API Optional Result Set Metadata” .

  • MYSQL_OPT_PROTOCOL (argument type: unsigned int * )

    Type of protocol to use. Specify one of the enum values of mysql_protocol_type defined in mysql.h .

  • MYSQL_OPT_READ_TIMEOUT (argument type: unsigned int * )

    The timeout in seconds for each attempt to read from the server. There are retries if necessary, so the total effective timeout value is three times the option value. You can set the value so that a lost connection can be detected earlier than the TCP/IP Close_Wait_Timeout value of 10 minutes.

  • MYSQL_OPT_RECONNECT (argument type: bool * )

    Enable or disable automatic reconnection to the server if the connection is found to have been lost. Reconnect is off by default; this option provides a way to set reconnection behavior explicitly. See Section 28.7.24, “C API Automatic Reconnection Control” .

  • MYSQL_OPT_RETRY_COUNT (argument type: unsigned int * )

    The retry count for I/O-related system calls that are interrupted while connecting to the server or communicating with it. The default value is 1 (1 retry if the initial call is interrupted for 2 tries total).

    This option can be used only by clients that link against a C client library compiled with NDB Cluster support.

  • MYSQL_OPT_SSL_CA (argument type: char * )

    The path name of the Certificate Authority (CA) certificate file. This option, if used, must specify the same certificate used by the server.

  • MYSQL_OPT_SSL_CAPATH (argument type: char * )

    The path name of the directory that contains trusted SSL CA certificate files.

  • MYSQL_OPT_SSL_CERT (argument type: char * )

    The path name of the client public key certificate file.

  • MYSQL_OPT_SSL_CIPHER (argument type: char * )

    The list of permitted ciphers for SSL encryption.

  • MYSQL_OPT_SSL_CRL (argument type: char * )

    The path name of the file containing certificate revocation lists.

  • MYSQL_OPT_SSL_CRLPATH (argument type: char * )

    The path name of the directory that contains files containing certificate revocation lists.

  • MYSQL_OPT_SSL_FIPS_MODE (argument type: unsigned int * )

    Controls whether to enable FIPS mode on the client side. The MYSQL_OPT_SSL_FIPS_MODE option differs from other MYSQL_OPT_SSL_ xxx options in that it is not used to establish encrypted connections, but rather to affect which cryptographic operations are permitted. See Section 6.6, “FIPS Support” .

    Permitted option values are SSL_FIPS_MODE_OFF , SSL_FIPS_MODE_ON , and SSL_FIPS_MODE_STRICT .

    Note

    If the OpenSSL FIPS Object Module is not available, the only permitted value for MYSQL_OPT_SSL_FIPS_MODE is SSL_FIPS_MODE_OFF . In this case, setting MYSQL_OPT_SSL_FIPS_MODE to SSL_FIPS_MODE_ON or SSL_FIPS_MODE_STRICT causes the client to produce a warning at startup and to operate in non-FIPS mode.

  • MYSQL_OPT_SSL_KEY (argument type: char * )

    The path name of the client private key file.

  • MYSQL_OPT_SSL_MODE (argument type: unsigned int * )

    The security state to use for the connection to the server: SSL_MODE_DISABLED , SSL_MODE_PREFERRED , SSL_MODE_REQUIRED , SSL_MODE_VERIFY_CA , SSL_MODE_VERIFY_IDENTITY . The default is SSL_MODE_PREFERRED . These modes are the permitted values of the mysql_ssl_mode enumeration defined in mysql.h . For more information about the security states, see the description of --ssl-mode in Section 6.4.2, “Command Options for Encrypted Connections” .

  • MYSQL_OPT_TLS_VERSION (argument type: char * )

    Which protocols the client permits for encrypted connections. The value is a list of one or more comma-separated protocol names. The protocols that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers” .

  • MYSQL_OPT_USE_RESULT (argument: not used)

    This option is unused.

  • MYSQL_OPT_WRITE_TIMEOUT (argument type: unsigned int * )

    The timeout in seconds for each attempt to write to the server. There is a retry if necessary, so the total effective timeout value is two times the option value.

  • MYSQL_PLUGIN_DIR (argument type: char * )

    The directory in which to look for client plugins.

  • MYSQL_READ_DEFAULT_FILE (argument type: char * )

    Read options from the named option file instead of from my.cnf .

  • MYSQL_READ_DEFAULT_GROUP (argument type: char * )

    Read options from the named group from my.cnf or the file specified with MYSQL_READ_DEFAULT_FILE .

  • MYSQL_REPORT_DATA_TRUNCATION (argument type: bool * )

    Enable or disable reporting of data truncation errors for prepared statements using the error member of MYSQL_BIND structures. (Default: enabled.)

  • MYSQL_SERVER_PUBLIC_KEY (argument type: char * )

    The path name of the file containing a client-side copy of the public key required by the server for RSA key pair-based password exchange. The file must be in PEM format. This option applies to clients that authenticate with the sha256_password or caching_sha2_password authentication plugin. This option is ignored for accounts that do not authenticate with one of those plugins. It is also ignored if RSA-based password exchange is not used, as is the case when the client connects to the server using a secure connection.

    If MYSQL_SERVER_PUBLIC_KEY is given and specifies a valid public key file, it takes precedence over MYSQL_OPT_GET_SERVER_PUBLIC_KEY .

    For information about the sha256_password and caching_sha2_password plugins, see Section 6.5.1.2, “SHA-256 Pluggable Authentication” , and Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication” .

  • MYSQL_SET_CHARSET_DIR (argument type: char * )

    The path name of the directory that contains character set definition files.

  • MYSQL_SET_CHARSET_NAME (argument type: char * )

    The name of the character set to use as the default character set. The argument can be MYSQL_AUTODETECT_CHARSET_NAME to cause the character set to be autodetected based on the operating system setting (see Section 10.4, “Connection Character Sets and Collations” ).

  • MYSQL_SHARED_MEMORY_BASE_NAME (argument type: char * )

    The name of the shared-memory object for communication to the server on Windows, if the server supports shared-memory connections. Specify the same value as the --shared-memory-base-name option used for the mysqld server you want to connect to.

The client group is always read if you use MYSQL_READ_DEFAULT_FILE or MYSQL_READ_DEFAULT_GROUP .

The specified group in the option file may contain the following options.

Option 描述
character-sets-dir= dir_name The directory where character sets are installed.
compress Use the compressed client/server protocol.
connect-timeout= seconds The connect timeout in seconds. On Linux this timeout is also used for waiting for the first answer from the server.
database= db_name Connect to this database if no database was specified in the connect command.
debug Debug options.
default-character-set= charset_name The default character set to use.
disable-local-infile Disable use of LOAD DATA LOCAL .
enable-cleartext-plugin Enable the mysql_clear_password cleartext authentication plugin.
host= host_name Default host name.
init-command= stmt Statement to execute when connecting to MySQL server. Automatically re-executed if reconnection occurs.
interactive-timeout= seconds Same as specifying CLIENT_INTERACTIVE to mysql_real_connect() . See Section 28.7.7.54, “mysql_real_connect()” .
local-infile[={0|1}] If no argument or nonzero argument, enable use of LOAD DATA LOCAL ; otherwise disable.