MySQL Learning notes (i)

Technology of the kingdom is too fascinating, I just engaged in it was seduced by various technologies, all want to learn. I still count word of that kind of person, fortunately have a little perseverance, so all sorts of things are contaminated a bit. But this kind of ubiquity doesn't really match my IQ. I don't have so much energy to learn so many things. Slowly to learn to give up some technology temporarily, first deepen the study of their work to use the things. such as MySQL and so on. For things that you particularly like, such as Linux, because it is too advanced and complex. I do not want to give up, then every day to the Linux one hours of time, familiar with the command, slowly gradually. I think I can spare a lot of time to study Linux after I have perfected the various skills I rely on to survive.

I was reading a book while knocking chestnuts, the title of "MySQL Core technology and best practice." While learning, see useful things on the jot down, so the notes will certainly appear a bit messy. Some knowledge points I have mastered before, maybe I will not write on it. This is just a self-learning note, so you can see here to stop, I do not want to waste your study time.

InnoDB Engine and MyISAM engine

MySQL is a relational database. One of the storage engines can be seen by show engines. My version is 5.6.26 and the view version is viewed with select version (). 5.6.26 's MySQL has 9 storage engines. One of the most common and commonplace is MyISAM and InnoDB. If the business is non-Thing (transcation) then both of these storage engines are similar, and there is no difference in performance. If you need most select queries in your business, you can use the MyISAM storage engine. If you need something, you need to use back InnoDB storage.

MySQL of Backup

In order to avoid data inconsistency during backup, write to database is not allowed during database backup.

Flush tables with read lock can be used;

The In-memory table data scan is now restored to the table. Then lock the table. A colleague gives Read permission. That is, when the database backup, still have Read permission.

After the backup operation for the database is complete, and then the database is unlocked. Tables unlock;

MySQL data type

Books on this data type structure of the picture is very good, to search the Internet, all flowers are not good-looking. So I still draw a bar, not much longer.

　　　

Hemp Egg, my drawing tool is broken, I summed up a knowledge, why bother.

The following is the text bar, the text to fast, labor-saving.

Decimal types are divided into exact decimal types and floating-point decimal types.

Decimal types include decimal exact types.

Floating point decimals, including single-precision and double-precision. (This I do not know what the meaning, interested in Baidu's bar, more commonly used is decimal). ‘

Decimal (length, precision). Lengh indicates how long this number is, and precision indicates how long it is after the decimal point.

MySQL String type

MySQL mainly supports six kinds of string types, char, varchar, TINYINTM, text, Mediumtext, Longtext.

I never knew the type of string above. Made a lot of mistakes. Read the book, and then after the experiment, I understand a lot.

The following is a direct reference to the third section of MySQL Core technologies and best practices.

char (n) is a fixed-length string type that represents a storage space that occupies n characters (note not bytes) and the maximum value of n is 255. For example, for the Chinese Simplified character Set GBK encoding, char (255) indicates that 255 kanji can be stored. Each character occupies two bytes of storage space. For a string of UTF8 character sets, char (255) means that 255 characters can be stored, and each character occupies 3 bytes of storage space.

VARCHAR (n) is a variable-length string type, which means that the storage space occupied by such a string is the storage space occupied by the string itself, regardless of N. This differs from char (n), which differs from char (n). For example, for the Chinese Simplified character set GBK string, varchar (255) indicates that 255 characters can be stored. Each character occupies two bytes of storage space. If the string does not have so many Chinese characters, such as just a "medium" character, then varchar (255) occupies only one character (2 bytes) of storage, if other costs are not considered. char (255) must occupy 255 characters of storage space, even if only one Chinese character is stored inside.

Storage space occupied by various character types:

　　

Date type of MySQL

MySQL mainly supports date types in 5. Date, Time,year,datetime,timestamp.

The date type. Format Support Month day. YYYY-MM-DD;

The time type.           Hours and seconds. HH:ii:ss.

DateTime is a combination of date and time. That is the day of the month. Time and seconds.

Timestamp is about the same as DateTime.

But the study can not pay attention to the same. Let's take a look at the difference between timestamp and DateTime.

The range of values represented is different, and the value range of datetime is much larger than the range of timestamp values. Timestamp was finished by 2037.

If timestamp is not assigned, the value of the field value is actually the current date and time of the MySQL server.

The date or time of the same timestamp type. Different loss shows different results. Using the MySQL command show varialbes like "Time_zone" can display the following information.

This system is used to indicate the time of the systems.

When a record that contains timestamp data is modified, the timestamp data is automatically updated to the current date and time of the MySQL server.

MySQL binary type

　　I should not have data types in MySQL data inventory binary, at least not currently involved. Just know that MySQL can save binary data, I think that's enough. There is a need to come back to study later.

A little sentiment:

(1) In the case of meeting the application requirements (value range accuracy), try to use a short data type.

(2) The more simple the data type, the better.

(3) Try to use the exact decimal type.

(4) Try to use the built-in date and time data type instead of using a string to store the date and time.

(5) Avoid null fields as far as possible, we recommend specifying the field as NOT NULL constraint. This is due to the fact that columns with null values in MySQL are difficult to query-optimized, that null values are indexed statistics, and that the comparison optimization operation becomes more complex. It is recommended to use 0, a special value, or an empty string instead of a null value.

MySQL Learning notes (i)