Some of MySQL's experiences

1, unsigned modified integer type, both non-negative, with this type can increase the length of data!

Type size range (signed) range (unsigned) use

TINYINT 1 bytes (-128,127) (0,255) Small integer value

SMALLINT 2 bytes (-32 768,32 767) (0,65 535) Large integer value

Mediumint 3 bytes (-8 388 608,8 388 607) (0,16 777 215) Large integer value

int or integer 4 bytes (-2 147 483 648,2 147 483 647) (0,4 294 967 295) Large integer value

2. How to create an association table: foreign key (the field to which the current table needs to be associated) reference the associated table (the field to be associated) on the DELETE cascade on UPDATE cascade

On update and on delete are an optional database foreign key definition that sets the transformation rules for the response field in the Foreign key table when the data of the referenced column in the primary key table changes. Update is the value of the referenced field in the primary key table, and delete refers to the deletion of a record in the primary key table, with four words followed by on update and on delete:

No action, set NULL, set default, Cascade
No action means no action,
Set NULL indicates that the corresponding field in the foreign-key table has a null
Set default means setting to defaults
Cascade represents cascading operations, that is, if the primary key table is updated in the Reference field, the Foreign key table is also updated, the records in the primary key table are deleted, and the row in the Foreign key table is deleted accordingly.

Sometimes when you create an association table error, be aware that the associated two tables must meet three conditions:

1. Two tables must be a INNODB data engine
2. Fields that use foreign key relationships must be indexed (index)
3. Fields that use foreign key relationships must be similar to data types

3. Setting the database engine, encoding type: Engine=innodb CHARACTER set UTF8 COLLATE utf8_general_ci

Engine=innodb, the database engine is InnoDB, and if you want to view the engine for a table, use the show create table name.

CHARACTER set UTF8, sets the character encoding of the table to UTF8

COLLATE Utf8_general_ci, set proofing rules for UTF8, the collation rule general characters are sorted and compared when it works.

4, if the field type is defined as int (n), but the actual data is less than n bits, you can specify Zerofill when the field is defined, then when the display width of the value is less than the specified column width, it will be supplemented with 0 on the left side of the data.

5. Change the index of the table:

Create a normal index: ALTER TABLE name add index index name (field name);

Create a unique index: ALTER TABLE name add unique (field name);

Create PRIMARY KEY index: ALTER TABLE name add primary key (field name);

Create GLOBAL index: ALTER TABLE name add fulltext (field name);

Create multi-column index: ALTER TABLE name add index index name (field name 1, field name 2);

Delete index: Drop the index name on the table name, or: ALTER TABLE name DROP INDEX name;

Some of MySQL's experiences