MySQLi（MySQL Improved）是一個用于PHP腳本語言的關系數據庫驅動，提供與MySQL的接口。它是PHP的一個擴展模塊，用于擴展PHP的核心功能。MySQLi擴展允許用戶訪問由 MySQL 4.1 或更高版本所提供的功能。雖然PHP原本就有能夠存取MySQL的函式庫，但是在MySQL 4.1.3版之后，PHP官方強烈推薦使用MySQLi。
MySQLi是PHP MySQL驅動的改進版本，提供了多種優點。它的名字來源于“MySQL Improved”的縮寫。MySQLi擴展允許用戶訪問由 MySQL 4.1 或更高版本所提供的功能。經 MySQL AB 授權，本文檔中包括部分 MySQL 手冊的內容。
需求
要使用本擴展庫中的函數，必須在編譯 PHP 時加入 mysqli 擴展的支持。
Note: mysqli 擴展庫是設計用于同 MySQL 4.1.3 或更高版本協同工作的。對之前版本，請參考 MySQL 擴展庫文檔。
安裝
要安裝 PHP 的 mysqli 擴展，配置時加上 --with-mysqli=mysql_config_path/mysql_config。其中 MySQL_config_path 表示 mysql_config 程序的路徑，該程序隨 MySQL 大于 4.1 版本一起發布。
如果要同時安裝 mysql 和 mysqli 擴展，必須使用同一個客戶端庫以避免沖突。
運行時配置
這些函數的行為受 PHP.ini 的影響。
MySQLi 配置選項 名稱 默認值 可修改范圍 更新日志
mysqli.max_links "-1" PHP_INI_SYSTEM 自 PHP 5.0.0 起可用。
mysqli.default_port "3306" PHP_INI_ALL 自 PHP 5.0.0 起可用。
mysqli.default_socket NULL PHP_INI_ALL 自 PHP 5.0.0 起可用。
mysqli.default_host NULL PHP_INI_ALL 自 PHP 5.0.0 起可用。
mysqli.default_user NULL PHP_INI_ALL 自 PHP 5.0.0 起可用。
mysqli.default_pw NULL PHP_INI_ALL 自 PHP 5.0.0 起可用。
以上 PHP_INI_* 常量的進一步細節及定義，見怎樣修改配置設定一節。
以下是配置選項的簡要解釋。
mysqli.max_links integer
每個進程的 MySQL 連接的最大數目。
mysqli.default_port string
The default TCP port number to use when connecting to the database server if no other port is specified. If no default is specified, the port will be obtained from the MYSQL_TCP_PORT environment variable, the mysql-tcp entry in /etc/services or the compile-時間 MYSQL_PORT constant, in that order. Win32 will only use the MYSQL_PORT constant.
mysqli.default_socket string
The default socket name to use when connecting to a local database server if no other socket name is specified.
mysqli.default_host string
The default server host to use when connecting to the database server if no other host is specified. Doesn't apply in safe mode.
mysqli.default_user string
The default user name to use when connecting to the database server if no other name is specified. Doesn't apply in safe mode.
mysqli.default_pw string
The default password to use when connecting to the database server if no other password is specified. Doesn't apply in safe mode.
預定義類
mysqli
表達了 PHP 和 MySQL 數據庫之間的連接。
構造函數
mysqli - 構造一個新的 mysqli 對象
方法
autocommit - 打開或關閉自動提交的數據庫選項
change_user - 改變指定的數據庫連接的用戶
character_set_name - 返回數據庫連接的默認字符集
close - 關閉一個之前打開的連接
commit - 提交當前事務
connect - 打開一個到 MySQL 數據庫服務器的新連接
debug - 執行排錯操作
dump_debug_info - 取得排錯信息
get_client_info - 返回客戶端版本
get_host_info - 返回連接使用的類型
get_server_info - 返回 MySQL 服務器的信息
get_server_version - 返回 MySQL 服務器的版本
init - 初始化 mysqli 對象
info - 取得最近執行的查詢的信息
kill - 要求服務器停止一個 mysql 線程
multi_query - 執行多個查詢
more_results - 檢查一個多語句查詢是否還有其他查詢結果集
next_result - 從 mysqli_multi_query() 中準備下一個結果集
options - 設置選項
ping - Ping 一個服務器連接，或者如果那個連接斷了嘗試重連
prepare - 準備一條用于執行的 SQL 語句
query - 在數據庫上執行查詢
real_connect - 打開一個到 MySQL 服務端的新連接
escape_string - 函數轉義 SQL 語句中使用的字符串中的特殊字符
rollback - 回滾當前事務
select_db - 改變連接的默認數據庫
set_charset - 設置默認客戶端字符集
ssl_set - 使用 SSL 建立安裝連接
stat - 返回當前系統狀態
stmt_init- 初始化一條語句并返回一個由 mysqli_stmt_prepare() 使用的對象
store_result - 傳輸最后一個查詢的結果集
thread_safe -返回是否設定了線程安全
use_result - 初始化一個結果集的取回
屬性
affected_rows - gets the number of affected rows in a previous MySQL operation
client_info - returns the MySQL client version as a string
client_version - returns the MySQL client version as an integer
errno - returns the error code for the most recent 函數 call
error - returns the error string for the most recent function call
field_count - returns the number of columns for the most recent query
host_info - returns a string representing the type of connection used
info - retrieves information about the most recently executed query
insert_id - returns the auto generated id used in the last query
protocol_version - returns the version of the MySQL protocol used
server_info - returns a string that represents the server version number
server_version - returns the version number of the server as an integer
sqlstate - returns a string containing the SQLSTATE error code for the last error
thread_id - returns the thread ID for the current connection
warning_count - returns the number of warnings generated during execution of the previous SQL statement
mysqli_stmt
表達了一個預備好的語句。
方法
bind_param - binds variables to a prepared statement
bind_result - binds variables to a prepared statement for result storage
close - closes a prepared statement
data_seek - seeks to an arbitrary row in a statement result set
execute - executes a prepared statement
fetch - fetches result from a prepared statement into bound variables
free_result - frees stored result memory for the given statement handle
prepare - prepares a SQL query
reset - resets a prepared statement
result_metadata - retrieves a resultset from a prepared statement for metadata information
send_long_data - sends data in chunks
store_result - buffers complete resultset from a prepared statement
屬性
affected_rows - returns affected rows from last statement execution
errno - returns errorcode for last statement 函數
error - returns errormessage for last statement function
field_count - returns the number of columns in a result set
id - returns the statement identifier
insert_id - returns the value generated for an AUTO_INCREMENT column by the prepared statement
num_rows - returns the number of rows in the result set
param_count - returns number of parameter for a given prepare statement
sqlstate - returns a string containing the SQLSTATE error code for the last statement 函數
mysqli_result
表達了對數據庫的查詢所返回的結果集。
方法
close - closes resultset
data_seek - moves internal result pointer
fetch_array - fetches a result row as an associative array, a numeric array, or both.
fetch_assoc - fetches a result row as an associative array
fetch_field - gets column information from a resultset
fetch_fields - gets information for all columns from a resulset
fetch_field_direct - gets column information for specified column
fetch_object - fetches a result row as an object
fetch_row - gets a result row as an enumerated array
field_seek - set result pointer to a specified field offset
free_result - frees result memory
屬性
current_field - returns offset of current fieldpointer
field_count - returns number of fields in resultset
lengths - returns an array of columnlengths
num_rows - returns number of rows in resultset
type - returns MYSQLI_STORE_RESULT or MYSQLI_USE_RESULT
預定義常量
MySQLi 常量 名稱 說明
MYSQLI_READ_DEFAULT_GROUP (integer) Read options from the named group from `my.cnf' or the file specified with MYSQLI_READ_DEFAULT_FILE
MYSQLI_READ_DEFAULT_FILE (integer) Read options from the named option file instead of from my.cnf
MYSQLI_OPT_CONNECT_TIMEOUT (integer) Connect timeout in seconds
MYSQLI_OPT_LOCAL_INFILE (integer) Enables command LOAD LOCAL INFILE
MYSQLI_INIT_COMMAND (integer) Command to execute when connecting to MySQL server. Will automatically be re-executed when reconnecting.
MYSQLI_CLIENT_SSL (integer) Use SSL (encrypted protocol). This option should not be set by application programs; it is set internally in the MySQL client library
MYSQLI_CLIENT_COMPRESS (integer) Use compression protocol
MYSQLI_CLIENT_INTERACTIVE (integer) Allow interactive_timeout seconds (instead of wait_timeout seconds) of inactivity before closing the connection. The client's session wait_timeout variable will be set to the value of the session interactive_timeout variable.
MYSQLI_CLIENT_IGNORE_SPACE (integer) Allow spaces after function names. Makes all functions names reserved words.
MYSQLI_CLIENT_NO_SCHEMA (integer) Don't allow the db_name.tbl_name.col_name syntax.
MYSQLI_CLIENT_MULTI_QUERIES (integer)
MYSQLI_STORE_RESULT (integer) For using buffered resultsets
MYSQLI_USE_RESULT (integer) For using unbuffered resultsets
MYSQLI_ASSOC (integer) Columns are returned into the array having the fieldname as the array index.
MYSQLI_NUM (integer) Columns are returned into the array having an enumerated index.
MYSQLI_BOTH (integer) Columns are returned into the array having both a numerical index and the fieldname as the associative index.
MYSQLI_NOT_NULL_FLAG (integer) Indicates that a field is defined as NOT NULL
MYSQLI_PRI_KEY_FLAG (integer) Field is part of a primary index
MYSQLI_UNIQUE_KEY_FLAG (integer) Field is part of a unique index.
MYSQLI_MULTIPLE_KEY_FLAG (integer) Field is part of an index.
MYSQLI_BLOB_FLAG (integer) Field is defined as BLOB
MYSQLI_UNSIGNED_FLAG (integer) Field is defined as UNSIGNED
MYSQLI_ZEROFILL_FLAG (integer) Field is defined as ZEROFILL
MYSQLI_AUTO_INCREMENT_FLAG (integer) Field is defined as AUTO_INCREMENT
MYSQLI_TIMESTAMP_FLAG (integer) Field is defined as TIMESTAMP
MYSQLI_SET_FLAG (integer) Field is defined as SET
MYSQLI_NUM_FLAG (integer) Field is defined as NUMERIC
MYSQLI_PART_KEY_FLAG (integer) Field is part of an multi-index
MYSQLI_GROUP_FLAG (integer) Field is part of GROUP BY
MYSQLI_TYPE_DECIMAL (integer) Field is defined as DECIMAL
MYSQLI_TYPE_NEWDECIMAL (integer) Precision 數學 DECIMAL or NUMERIC field (MySQL 5.0.3 and up)
MYSQLI_TYPE_BIT (integer) Field is defined as BIT (MySQL 5.0.3 and up)
MYSQLI_TYPE_TINY (integer) Field is defined as TINYINT
MYSQLI_TYPE_SHORT (integer) Field is defined as INT
MYSQLI_TYPE_LONG (integer) Field is defined as INT
MYSQLI_TYPE_FLOAT (integer) Field is defined as FLOAT
MYSQLI_TYPE_DOUBLE (integer) Field is defined as DOUBLE
MYSQLI_TYPE_NULL (integer) Field is defined as DEFAULT NULL
MYSQLI_TYPE_TIMESTAMP (integer) Field is defined as 時間STAMP
MYSQLI_TYPE_LONGLONG (integer) Field is defined as BIGINT
MYSQLI_TYPE_INT24 (integer) Field is defined as MEDIUMINT
MYSQLI_TYPE_DATE (integer) Field is defined as DATE
MYSQLI_TYPE_TIME (integer) Field is defined as TIME
MYSQLI_TYPE_DATETIME (integer) Field is defined as DATETIME
MYSQLI_TYPE_YEAR (integer) Field is defined as YEAR
MYSQLI_TYPE_NEWDATE (integer) Field is defined as DATE
MYSQLI_TYPE_ENUM (integer) Field is defined as ENUM
MYSQLI_TYPE_SET (integer) Field is defined as SET
MYSQLI_TYPE_TINY_BLOB (integer) Field is defined as TINYBLOB
MYSQLI_TYPE_MEDIUM_BLOB (integer) Field is defined as MEDIUMBLOB
MYSQLI_TYPE_LONG_BLOB (integer) Field is defined as LONGBLOB
MYSQLI_TYPE_BLOB (integer) Field is defined as BLOB
MYSQLI_TYPE_VAR_STRING (integer) Field is defined as VARCHAR
MYSQLI_TYPE_STRING (integer) Field is defined as CHAR
MYSQLI_TYPE_GEOMETRY (integer) Field is defined as GEOMETRY
MYSQLI_NEED_DATA (integer) More data available for bind variable
MYSQLI_NO_DATA (integer) No more data available for bind variable
MYSQLI_DATA_TRUNCATED (integer) Data truncation occurred. Available since PHP 5.1.0 and MySQL 5.0.5.
例子
在 MySQLI 文檔中的所有例子都使用了來自 MySQL AB 的 world 數據庫。數據庫 world 可以在此地址下載： http://dev.mySQLcom/get/Downloads/Manual/world.sql.gz/from/pick。
范例
mysql的連接方式
一、面向對象
PHP
$mysqli =new mysqli("localhost", "my_user", "my_password", "world"); //實例化對象
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
printf("Host information: %s\n", $mysqli->host_info);
/* close connection */
$mysqli->close();
?>
二、面向過程
$link =mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (!$link) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
printf("Host information: %s\n", mysqli_get_host_info($link));
/* close connection */
mysqli_close($link);
?>
參考資料 >