MySQL Optimization-simple syntax and mysql Optimization

MySQL Optimization-simple syntax and mysql Optimization

Reprinted please indicate the source: http://blog.csdn.net/l1028386804/article/details/46559271

1. Default Constraints

    --mysql    CREATE TABLE emp    (    id INT DEFAULT 12    )

2. Set the auto-incrementing MYSQL column to an indexed column. Set the seed value after the table.

-- Mysql -- set auto-increment ID from n to create table emp (id int primary key AUTO_INCREMENT) AUTO_INCREMENT = 100; -- (Set auto-increment ID to start from 100)

The step size of the auto-increment column can be divided into the global level and the session level. If it is the session level, when you create a new session, the step size is back to the global level. mysql cannot be set as the table-level step size !!
The mysql Server maintains two mysql system parameters (system variables): global variables and session variables ).
Their meanings and differences are shown in their respective names. session variables are at the session level, and changes to them only affect the current session; global variables are system-level,
The change affects all new sessions (the existing session is not affected when the change is made) until the next mysql server restarts.
Note that the change impact cannot be different from the restart. To use the new value when mysql server is restarted, you must specify the variable option or change the option file on the command line,
However, the cross-Restart mode cannot be implemented through the SET change.
Each system variable has a default value, which is determined when the mysql system is compiled.
Specify system variables. Generally, you can specify options in the command line or in the option file when the server is started.
Of course, you can use the set command to specify the value of most system variables during system running.
View the default auto-incrementing column seed and step size.

Show global variables like 'Auto _ incret % '; -- GLOBAL variable

Q: if there is a table with an auto-increment primary key whose field is id, after 10 data entries have been inserted into the table, the data with id and 10 has been deleted, restart mysql,
Then insert a data record. What is the id of the data record, which is 8 or 11?
A: If the table type is MyISAM, it is 11. If the table type is InnoDB, the id is 8.
This is because the two types of storage engines store the maximum ID records in different ways, MyISAM table records the maximum ID to the data file, the maximum ID value of the auto-incrementing primary key of mysql is not lost;
While InnoDB records the maximum ID value to the memory. Therefore, after you restart mysql or OPTIMIZE the table, the maximum ID value will be lost.
By the way, MYSQL can get the auto-increment value of the current table.

(1) select max (id) FROM person for a specific table (2) SELECT LAST_INSERT_ID () function for any table (3) SELECT @ identity for any table

@ Identity refers to the value of the auto-incrementing column corresponding to the last time data is inserted into a table with the identity attribute (that is, the auto-incrementing column). It is a global variable defined by the system.
Generally, global variables defined by the system start with @ and User-Defined variables start.
The premise of using @ identity is that the connection is not closed when select @ identity is executed after the insert operation. Otherwise, the value is NULL.

(4)  SHOW TABLE STATUS LIKE 'person' 

This method is recommended for specific tables.
In the result, the corresponding table name record contains an Auto_increment field. The value of the next auto-increment ID is the maximum auto-increment ID of the current table.
3. View table Definitions

 DESC emp

4. Modify the table name

ALTER TABLE emp RENAME emp2

5. Modify the Data Type of the field and change the int type of the id field to bigint.

ALTER TABLE emp2 MODIFY id BIGINT

6. When modifying the field name in MYSQL, you must add the Data Type of the field. Otherwise, an error will be reported. CHANGE can only MODIFY the data type to achieve the same effect as MODIFY, the method is to set the "new field name" and "old field name" in the SQL statement to the same name, only change the "data type" and change the data type. For example, in the example just now, change the id column to the bigint data type.

ALTER TABLE emp2 CHANGE id id BIGINT

7. Add Fields

ALTER TABLE emp2 ADD NAME NVARCHAR(200)  NULL

8. delete a field MYSQL does not need to add the COLUMN keyword to delete a field

 ALTER TABLE emp2 DROP NAME

9. If the foreign key constraint is deleted, the drop foreign key must be used. If the foreign key constraint is used, the drop primary key must be used.
-- Delete foreign key constraints

 ALTER TABLE emp2 DROP FOREIGN KEY fk_emp_dept

-- Delete the primary key constraint

 ALTER TABLE emp2 DROP PRIMARY KEY pk_emp_dept

10. delete a table

DROP TABLE emp2

However, if you want to delete multiple tables at the same time or before deleting them, you must first determine

DROP TABLE IF EXISTS emp1 ,emp2

Supplement:

USE test; -- myisam engine create table test (ID int unsigned not null auto_increment, name varchar (10) not null, key (name, id) engine = MYISAM auto_increment = 100; -- innodb engine create table TESTIdentity (ID int unsigned not null auto_increment, nid int unsigned, name varchar (10) not null, key (id) engine = INNODB auto_increment = 100; -- or the primary key create table TESTIdentity (ID int unsigned not null auto_increment, nid int unsigned, name varchar (10) not null, key (id) engine = INNODB auto_increment = 100; [Database4] ErrorCode:-2147467259, Number: 1075 ErrorMessage: Incorrect table definition; there can be only one auto column and it must be defined as a keyalter table TESTIdentity modify column nid int auto_increment;

No matter the innodb engine or MYISAM engine table, there can only be one auto-incrementing column, and the auto-incrementing column must be an index column, whether it is a secondary index or a primary key index