day41

Numeric type

Integer type

< img alt="share picture" src="/wp-content/uploads/images/database/mysql/1626816536461.png" >

signed settings

mysql> create table t1(id tinyint); # The default is signed, that is, there is a sign before the number

Unsigned setting

mysql> create table t1(id tinyint unsigned);

zerofill usage

If the width is less than the setting The width of (here, the width is 4), it is automatically filled with 0. It should be noted that this is only the last displayed result, and the actual storage in MySQL is still 1

Use zerofill to test the display width of integer types< /p>

mysql> create table t7(x int(3) zerofill); mysql> insert into t7 values ​​-> (1), -> (11), -> (111),- > (1111); mysql> select * from t7; +------+ | x | +------+ | 001 | | 011 | | 111 | | 1111 | #Exceed the width limit and still can save +------+

Note: For integer types, the width behind the data type is not the storage length limit, but the display limit. If: int(8), then the display is not enough 8 bits are filled with 0, and if 8 bits are enough, it will be displayed normally. Tested by zerofill, the storage length is still 4 bytes of int. The default display width is the length of the largest data that can be stored, such as: int unsigned type, then the default display width is int(10), and the signed one is int(11), because there is one more symbol, so we don’t have It is necessary to specify the data of the integer type, and there is no need to specify the width, because the original data you save can be completely displayed by default

The storage width of int is 4 Bytes, which is 32 bits, which is 2** 32

The unsigned maximum value is: 4294967296-1

The signed maximum value: 2147483648-1

? Signed and unsigned maximum numbers are required The display width is 10, and for the signed minimum, 11 bits are required to display completely, so the default display width of the int type is 11 is very reasonable.

　　 Finally: the plastic type, there is no need to specify Display width, use the default ok

MySQL mode

Check sql_mode

select @@sql_modeselect @@global.sql_mode

sql_mode is commonly used to solve the following types of problems

• By setting the sql mode, data verification of different degrees of strictness can be completed, effectively guaranteeing data Preparedness.
• By setting the sql model to a relaxed mode, to ensure that most sql conforms to the standard sql grammar, so that when the application is migrated between different databases, there is no need to make major changes to the business sql.
• Before data migration between different databases, by setting SQL Mode, the data on MySQL can be migrated to the target database more conveniently.

Common values ​​of sql_mode are as follows

Briefly introduce the details: https://www.cnblogs.com/clschao/articles/9962347.html

NO_ENGINE_SUBSTITUTION loose version-if the required storage engine is disabled or not compiled, then an error will be thrown. When this value is not set, replace it with the default storage engine, and throw an exception STRICT_TRANS_TABLES strict version-in this mode, if a value cannot be inserted into a transaction table, the current operation is interrupted, and the non-transaction table is not Do restriction NO_ZERO_IN_DATE: In strict mode, the date and month are not allowed to be zero

Note: MySQL5.6 and MySQL5.7 default sql_mode mode parameters are different

Note: p>

• 5.6 is NO_ENGINE_SUBSTITUTION, in fact it means a null value, which is equivalent to no mode setting, it can be understood as relaxed mode
• 5.7’s mode is STRICT_TRANS_TABLES, which is strict mode .

If the relaxed mode is set, when we insert data, even if we give an incorrect data, it may be accepted without reporting an error. For example: when I create a table, a field in the table is name, and the field type set for name is char(10). If I insert data, the length of a piece of data corresponding to the name field exceeds If the value is 10, for example, '1234567890abc', if it exceeds the set field length of 10, then no error will be reported, and the first ten characters will be saved, which means that your data is stored as '1234567890', but'abc' is not Yes, but we know that the data we gave is wrong, because it exceeds the field length, but no error was reported, and mysql processed and accepted it by itself. This is the effect of the loose mode. In fact, it is in development, testing, production, etc. In the environment, we should adopt strict mode. If this kind of error occurs, it should be reported. Therefore, MySQL 5.7 version changed the default value of sql_mode to strict mode, and even if we use MySQL 5.6, we should change it by ourselves. It’s changed to strict mode, and you remember, MySQL and other databases want to take care of all operations on data by themselves, including data verification. In fact, many times, we should develop project programs by ourselves. The level has done these verifications. Although there are some troublesome steps when writing the project, after doing this, it will be much more convenient for us to perform database migration or project migration. This is up to you to measure. In addition to data verification, mysql will discover as you slowly learn that it can do a lot of things, and it will take care of a lot of things you do in your program. 

Problems that may exist after changing to strict mode

If NO_ZERO_DATE is included in the setting mode, then the MySQL database does not allow the insertion of a zero date, and inserting a zero date will Throw errors instead of warnings. For example, the table contains the field TIMESTAMP column (if it is not declared as NULL or displays the DEFAULT clause), DEFAULT '0000-00-00 00:00:00' (zero timestamp) will be automatically assigned, or it is the day column of the test table By default, zero date '0000-00-00' COMMENT'date' is allowed to be inserted; these obviously do not satisfy NO_ZERO_DATE in sql_mode and report an error. 

Mode setting and modification method

• Method one

  Execute select @@sql_mode first, copy the query Value and delete NO_ZERO_IN_DATE, NO_ZERO_DATE, and then execute set sql_mode ='modified value' or set session sql_mode='modified value';, for example: set session sql_mode='STRICT_TRANS_TABLES'; change to strict mode this method It only takes effect in the current session, and does not take effect when closing the current session. 

• Method two

  First execute select @@global.sql_mode, copy the value in the query and delete NO_ZERO_IN_DATE, NO_ZERO_DATE , And then execute set global sql_mode ='modified value'. This method takes effect in the current service, and it becomes invalid after restarting the MySQL service

• Method 3

  Under the mysql installation directory, or my.cnf file (my.ini file for windows system), add sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ENGINE_SUBSTITUTION,restart my.cnf,restart,restart,restart,my.cnf=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ENGINE_SUBSTITUTION,restart,restart my. This method takes effect permanently. Of course, it is forbidden to restart the MySQL service in the production environment, so the method two plus the method three is used to solve the online problem, then even if the MySQL service is really restarted one day, it will take effect permanently. 

float type

Note: float can only be accurate to 8 digits, starting from the integer and going from left to right

test mysql> create table t3(id float(60,30));Query OK, 0 rows affected (1.70 sec)mysql> create table t4(id double(60,30));Query OK, 0 rows affected (0.88 sec)mysql> create table t5(id decimal(60,30));Query OK, 0 rows affected (0.96 sec)mysql> insert into t3 values(1.1111111111111111111111);Query OK, 1 row affected (0.13 sec)mysql> insert into t4 values(1.1111111111111111111111 );Query OK, 1 row affected (0.22 sec)mysql> insert into t5 values(1.1111111111111111111111);Query OK, 1 row affected (0.09 sec)mysql> select * from t3;mysql> select * from t4;mysql> select * from t5;

Bit Type

Can store binary numbers or hexadecimal numbers

For details, please see: https:// www.cnblogs.com/clschao/articles/9959559.html

date type

• year

YYYY (range: 1901~2155) ——2018

• date

YYYY-MM-DD (range: 1000-01-01~9999-12-31) ——2018-01-01

• time

HH:MM:SS ——12:09:32

• datetime

YYYY-MM-DD HH:MM:SS (range: 1000-01-01 00:00:00~9999-12-31 23:59:59)——2018-01-01 12:09:32 

Test

mysql> create table t6(d1 year ,d2 date,d3 datetime);Query OK, 0 rows affected (1.75 sec)mysql> insert into t6 values(now(),now(),now());Query OK, 1 row affected, 1 warning (0.12 sec)mysql> select * from t6;

String type

Details: https://www.cnblogs.com/clschao/articles/9959559.html

For the char type, mysql will store less than the specified length when storing The data is completed in the form of supplementary spaces at the back (complete on the right), and then stored in the hard disk, but when reading or using it, it will automatically remove the contents of the completed spaces for you, because these spaces are not our own The stored data is useless for our users.

Char and varchar performance comparison

Compared with char(5) and varchar(5), add me to save three names: sb, ssb1, ssbb2
　　　　char ：
　　　　　　 Advantages: Simple and crude, no matter how long you are, I will save the data according to the specified length. If you save 5 and 5, the three names will be stored like this: sb ssb1 ssbb2, the middle is Completion of spaces, when fetching data, 5 out of 5 are fetched, simple and crude, fast speed
　　　　　　 Disadvantages: It looks like a waste of space, and the length of the data we store in the future may be uneven.

 varchar:
　　　　　　varchar type stores data with variable length, which is more streamlined and saves space.
　　　　　　 For example, when storing the above three names, it is similar to this: sbssb1ssbb2, connected, if these three people are saved like this, please ask How do you take out the name? Do you know how long you can take out the first one? (Super brother, I can see it, then I just want to say: Roll the calf!)
　　　　　　 I don’t know where to start and where to end. When you encounter such a problem, how would you think of how to solve it? Still remember it? Think about it? socket? tcp? struct? Use the data length as the message header. 

　　　　　　
　　　　　　 Therefore, when varchar stores data, it will add a header before each data. This header is 1-2 bytes of data. This data refers to the following The length of this data, 1bytes can represent 2**8=256, and two bytes represent 2**16=65536, which can represent a number from 0 to 65535, so varchar is stored like this: 1bytes+sb+1bytes +ssb1+1bytes+ssbb2, so it will be more troublesome to save, resulting in slower efficiency than char, and slower when fetching, first get the length, then fetch the data. 

　　　　　　 Advantages: It saves some hard disk space. An acsii code character can be represented by a byte length, but it is not necessarily less than char. Look at a table comparison data given on the official website. When the data you store is exactly the length of the field you specified, varchar occupies more space than char. 

Value CHAR(4) Storage Required VARCHAR(4) Storage Required
'' '' 4 bytes'' 1 byte
'ab''ab '4 bytes'ab' 3 bytes
'abcd''abcd' 4 bytes'abcd' 5 bytes
'abcdefgh''abcd' 4 bytes'abcd' 5 bytes
　　　　　　　　　　　　
　　　　　　　 Disadvantages: access speed Slow
　　Summary:
　　　　So you need to choose which type to save according to business needs.
　　　　 In fact, in most work scenarios with a small number of users, the efficiency of char and varchar is not very different, at least The perception to users is not very large, and in fact, the software level is far slower than the hardware level. Therefore, when your company's operation and maintenance finds that the project is slow, it will add memory and change the nb hard disk, and the efficiency of the project will be improved a lot. , But as professionals, we should put forward such technical points to improve efficiency. 

　　　　 But for the InnoDB data table, the internal row storage format does not distinguish between fixed-length and variable-length columns (all data rows use the header pointer to the data column value), so in essence, use fixed Length CHAR columns are not necessarily better than using variable-length VARCHAR columns. Thus, the main performance factor is the total amount of storage used by the data row. Since CHAR occupies more space than VARCHAR on average, it is better to use VARCHAR to minimize the total storage and disk I/O of the data rows that need to be processed. 

　　　　 So, you can choose either. If it is a large-scale concurrent project, when you are pursuing high performance, you need to test with the hardware environment of your server. See which char or varchar is better. This can also be considered an optimization point~~~~

enumeration type and collection type

test:

< pre class="python">An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.) Example: CREATE TABLE shirts (name VARCHAR(40),size ENUM( ‘x-small’,’small’,’medium’,’large’,’x-large’) ); INSERT INTO shirts (name, size) VALUES (‘dress shirt’,’large’), (‘t- shirt’,’medium’),(‘polo shirt’,’small’); The set type (set) cannot be a number, it can become a string A SET column can have a maximum of 64 distinct members. Example: CREATE TABLE myset (col SET(‘a’,’b’,’c’,’d’)); INSERT INTO myset (col) VALUES (‘a,d’), (‘d,a’), (‘a,d ,a’), (‘a,d,d’), (‘d,a,d’);

Integrity Constraint

Details Please see: https://www.cnblogs.com/clschao/articles/9968396.html

not null not empty

create table t1(name char(10) not null);

default default value

create table t1(name char(10) not null default'xx');

unique is unique, field data cannot be repeated

create table t1(name char(10) unique);

primary key primary key, constraint effect: not empty And unique

create table t1(id int primary key);

auto_increment auto-increment

create table t1(id int primary key auto_increment); The front must be a key

foreign key Foreign key, create table Constraints for the use of relationships

• Many-to-one (one-to-many)



Add a field to the multi-table, and add a foreign key to this field, for example: the publisher has more books One-to-one relationship 1. First create the publisher table publish table 2. Create the book table, foreign key writing: create table book( id int primary key, name char(10), pid int, foreign key(pid) references publish(id ) ); 3. Insert data for the publisher first

• One-to-one relationship



Student table (student) and customer table (customer) add fields to the table with less data, if there are as many as any table, create table student( id int primary key, name char(10), cid int unique, foreign key(cid) references customer(id) );

Get qq in the student table



• Many-to-many relationship



The author table and the book table need to use the third table to complete the two The third table of the relationship record of the author is finally created create table authortobook( id int primary key, aurhor_id int, book_id int, foreign key(author_id) references author1(id), foreign key(book_id) references book1(id) )

Find the code: