This article mainly introduces thinkphp's database backup method sharing, which is very simple and practical. it is recommended that THINKPHP has no database backup method, so I wrote one myself, pdo is used for database connection and transaction processing. if necessary, contact me to write a mysql or mysqli
The code is as follows:
<? Php
Class SqlAction extends Action {
Function outsql (){
Header ("Content-Type: text/html; charset = utf-8 ″);
/* Use the C method to read the database configuration */
$ Host = C ('Db _ host ');
$ Db_name = C ('Db _ name ');
$ User = C ('Db _ user ');
$ Password = C ('Db _ pwd ');
/* Call the private method for exporting the database */
$ Outstream = $ this-> outputSql ($ host, $ dbname, $ user, $ password );
/* Download and export the database */
Header ("Content-Disposition: attachment; filename = $ dbname. SQL ");
Echo $ outstream;
}
/*
* Database export function outputSql
* Export database data in PDO mode
* $ Host name, such as localhost
* $ Dbname database name
* $ User name
* $ Password
* $ Flag 0 or 1 0 indicates that only the database structure is exported. 1 indicates that the exported database structure and data are 1 by default.
*/
Private function outputSql ($ host, $ dbname, $ user, $ password, $ flag = 1 ){
Try {
$ Pdo = new PDO ("mysql: host = $ host; dbname = $ dbname", $ user, $ password); // connect to the database
$ Pdo-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION); // sets the optimization parameter and throws an exception when an error occurs.
} Catch (PDOException $ e ){
Echo $ e-> getMessage (); // if a connection exception occurs, an error message is thrown.
Exit;
}
$ Mysql = "drop database if exists '$ dbname'; \ n"; // $ mysql loads SQL statements. IF a DATABASE EXISTS, drop the DATABASE.
$ Creat_db = $ pdo-> query ("show create database $ dbname")-> fetch (); // use show create database to view SQL statements
Preg_match ('/default character set (. *) \ */', $ creat_db ['create database'], $ matches); // Obtain the character set after the DEFAULT CHARACTER SET
$ Mysql. = "create database '$ dbname' default character set $ matches [1]"; // This statement is shown in create database 'test _ db' default character set utf8
/* Search for the full collation of the database, such as COLLATE utf8_general_ci */
$ Db_collate = $ pdo-> query ("SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '$ dbname 'limit 1')-> fetch ();
$ Mysql. = "COLLATE". $ db_collate ['default _ COLLATION_NAME ']. "; \ nUSE' $ dbname'; \ n ";
$ Statments = $ pdo-> query ("show tables"); // return result set, show tables view all table names
Foreach ($ statments as $ value) {// traverses this result set and exports the information corresponding to each table name
$ Table_name = $ value [0]; // Obtain the table name
$ Mysql. = "drop table if exists '$ table_name'; \ n"; // prepare the Drop statement before each TABLE.
$ Table_query = $ pdo-> query ("show create table '$ table_name'"); // retrieves the result set of table creation information for this table.
$ Create_ SQL = $ table_query-> fetch (); // use the fetch method to retrieve the array corresponding to the result set.
$ Mysql. = $ create_ SQL ['create Table']. "; \ r \ n"; // write Table creation information
If ($ flag! = 0) {// if the flag is not 0, continue to retrieve the table content and generate an insert statement.
$ Iteams_query = $ pdo-> query ("select * from '$ table_name'"); // retrieves the result set of all fields in the table
$ Values = ""; // prepare an empty string to load the insert value.
$ Items = ""; // prepare an empty string to load the field name of the table
While ($ item_query = $ iteams_query-> fetch (PDO: FETCH_ASSOC) {// returns the array of field names and values in the table using join queries.
$ Item_names = array_keys ($ item_query); // retrieves the key value of this array, that is, the field name.
$ Item_names = array_map ("addslashes", $ item_names); // translate special characters \
$ Items = join ('','', $ item_names); // The Union field name, for example, items1'. the 'item2' symbol is the field name next to the backquotes keyboard 1, which is enclosed by backquotes.
$ Item_values = array_values ($ item_query); // retrieves the value of this array, that is, the value corresponding to the field.
$ Item_values = array_map ("addslashes", $ item_values); // translate special characters \
$ Value_string = join ("','", $ item_values); // union values such as value1 'and 'value2 values are enclosed in single quotes.
$ Value_string = "('". $ value_string. "'),"; // The values are enclosed by parentheses.
$ Values. = ". $ value_string; // returns the result to $ value.
}
If ($ values! = "") {// If $ values is not empty, the table has content
// Write the insert statement
$ Insert_ SQL = "INSERT INTO '$ table_name' ('$ items') VALUES". rtrim ($ values, ","). "; \ n \ r ";
// Write this statement into $ mysql
$ Mysql. = $ insert_ SQL;
}
}
}
Return $ mysql;
}
}
?>
Is it a very practical function? friends can directly transplant it to their own projects.