MySQL changes the table storage engine, MySQL storage engine

MySQL changes the table storage engine, MySQL storage engine
MySQL provides multiple database storage engines that store and extract data from MySQL databases. Different storage engines have different features. Sometimes you may need to convert an existing table storage engine into another storage engine. There are many ways to complete this conversion, each method has advantages and disadvantages and should be selected based on the actual situation. The following describes three methods to convert the MySQL table engine:
ALTER TABLEThe simplest way to convert the MySQL TABLE engine is to directly use alter table to modify the TABLE definition. The following statement converts the mytable ENGINE to InnoDBmysql> alter table mytable ENGINE = InnoDB. Advantages: simple and can be used by any table engine. Disadvantage: it takes a long time to execute. The conversion process is to first define a new engine table and then copy the data in the original table. In the process of replication, the system consumes a lot of I/O capabilities, and the read lock is added to the original table. Therefore, using this conversion method in a very busy system won't work, even though it is very simple. In addition, it should be noted that if you use this method to convert the table engine, the features related to the original table engine will be lost. For example, if you convert an InnoDB table to a MyISAM table and then to an InnoDB table, the foreign key in the original table disappears even if nothing is done.
Import and ExportTo better control the conversion process, you can use the mysqldump tool to export data to a file, and then modify the ENGINE option of the create table statement in the file. Pay attention to modifying the TABLE name, because a database cannot have two tables with the same name, they use different storage engines in real time.SHELL>Mysqldump-u user name-p database name Table Name> file nameVi: Modify the create table statement in the fileMysql>Source with path file name

Create and queryThe third approach combines the first approach to efficiency and the second approach to security. You do not need to export the data of the entire table. Instead, you need to create a new storage engine table and then use the INSERT... SELECT syntax to export the data.Mysql>Create table innodb_table LIKE myisam_table;Mysql>Alter table innodb_table ENGINE = InnoDB;Mysql>Insert into innodb_table SELECT * FROM myisam_table;
MYSQL storage engine problems

View default and supported storage engines
Mysql> show engines;

View the storage engine of a table
Mysql> show create table users;

To modify the storage engine, you must specify the storage engine when creating a table or modify it later.
Create table mytable (id int, titlechar (20) ENGINE = INNODB

Alter table engineTest ENGINE = INNODB;

The default storage engine cannot be modified in real time (at least I have not found any modification method)

How to set the storage engine in mysql? How can I change INNODB to MYISAM? How can I save databases, tables, and other objects in MYSQL in DOS?

1. How do I set the storage engine in mysql? ------------ The default value is myisam, which is also specified during table creation. For example, create table test (id int) engine = innodb;
2. How can I change INNODB to MYISAM? ------------------ Alter table test engine = myisam;
3. How to save databases, tables, and other objects in MYSQL in DOS? ----------------- Execute create database databasename; create table test (id int) in dos. In this way, the database and table are generated. The corresponding system file is under data in the mysql installation directory, the database name corresponds to a folder. For example, if you create database testdb, you can find the testdb directory under the data Directory. For objects such as tables, you need to check the specific engine. For myisam engine, there will be three files, test. frm, test. myi, test. there are three myd, and innodb only has one test. frm structure files, data and index files are all in the ibdata1 tablespace.
4. How does PHP connect to MYSQL? Do you have to enter the code? Is there any other simple method, such as UI-based setting -----------------. You need to write the connection information and I have found a php connection to mysql online. For details, refer
<? Php
$ Mysql_server_name = 'localhost'; // change it to your mysql database server.
$ Mysql_username = 'root'; // change the username of your mysql database.
$ Mysql_password = '000000'; // change the password of your mysql database.
$ Mysql_database = 'mydb'; // change the name of your mysql database.

$ Conn = mysql_connect ($ mysql_server_name, $ mysql_username, $ mysql_password, $ mysql_database); // explain down from the beginning of this sentence

$ SQL = 'insert into book (name, pwd) values ("ggg", "ggg ");';
// This is an SQL statement: Insert a record into the book table

Mysql_query ($ SQL );
// Execute the SQL statement

Mysql_select_db ($ mysql_database, $ conn); // select the database where the above table is located (this statement should be executed before the above sentence)

$ Result = mysql_query ($ SQL); // This sentence is completely redundant. It is the same as the one above!

Mysql_close ($ conn); // close the database connection
Echo "Hello! Operation successful! "; // Display prompt information

?>