Mysql study notes (1)

The kingdom of technology is so fascinating. As soon as I started working in IT, I was seduced and confused by various technologies and wanted to learn everything. I am still the kind of person who keeps my word, but fortunately I have a little bit of perseverance, so I got a little bit of all kinds of things. But this ubiquitous situation really doesn’t match my IQ. I don't have that much energy to learn so many things. Slowly learn to temporarily give up some technologies, and first deeply study the things you need to use in your work. Such as mysql, etc. For things that I particularly like, such as Linux, it is too advanced and complicated. I don't want to give up, so I will give Linux an hour every day to get familiar with the commands and gradually progress. After the various skills that I rely on for my survival are more perfect, I think I can spare a lot of time to study Linux.

I was reading a book while thinking about it. The title of the book is "MySQL Core Technology and Best Practices". While studying, I jot down anything useful when I see it, so my notes will definitely look a little messy. I have already mastered some knowledge points before, so maybe I won’t write about them. This is just a self-study note, so you can stop here. I don’t want to waste your study time.

InnoDB engine and MyISAM engine

MySQL is a relational database. The storage engines can be viewed using show engines. My version is 5.6.26. To view the version, use select version() to view it. MySQL 5.6.26 has 9 storage engines. The most common and clichéd ones are MyISAM and InnoDB. If the business is non-transcation, then the two storage engines are similar and there is no difference in performance. If most select queries are required in the business, the MyISAM storage engine can be used. If you need things, you need to use InnoDB storage.

Backup of MySQL

In order to avoid data inconsistency during backup, writing operations to the database are not allowed during the database backup.

You can use flush tables with read lock;

Now scan the table data in the memory and restore it to the table. Then lock the table. Colleagues grant read permission. In other words, when the database is backed up, there is still read permission.

Wait until the database backup operation is completed, and then unlock the database. tables unlock;

MySQL data type

The pictures of this data type structure in the book are quite good, but after searching online, they are not very good. So I might as well draw one myself, it won't take long.

Ma Dan, my drawing tool is broken, I will just sum up my knowledge, why bother.

The next step is text. Text comes quickly and saves effort.

Decimal types are divided into precise decimal types and floating point decimal types.

Decimal types include decimal exact types.

Floating point decimals, including single and double precision. (I don’t know what this means. If you are interested, check it out on Baidu. The more commonly used one is decimal). '

decimal(length, precision) . Lengh represents the total length of the number, and precision represents the length after the decimal point.

MySQL’s string types

　MySQL mainly supports six string types, char, varchar, tinyintm, text, mediumtext, longtext.

　I didn’t know the string types clearly before. Made many mistakes. After reading the book and experimenting, I understood a lot.

　The following content directly quotes the third chapter of "MySQL Core Technology and Best Practices".

char(n) is a fixed-length string type, which represents the storage space occupied by n characters (note not bytes). The maximum value of n is 255. For example, for the Chinese simplified character set gbk encoding, char(255 ) means that 255 Chinese characters can be stored, and each Chinese character occupies two bytes of storage space. For a string in the utf8 character set, char(255) means that 255 Chinese characters can be stored, and each Chinese 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 is different from char(n), which is different from char(n). For example, for the string of the simplified Chinese character set gbk, varchar(255) means that 255 Chinese characters can be stored. Each Chinese character occupies two bytes of storage space. If this string does not contain so many Chinese characters, for example, it only includes one "中" character, then varchar(255) only takes up one character (2 bytes) of storage space, if other overhead is not considered. And char(255) must occupy 255 characters of storage space, even if only one Chinese character is stored in it.

Storage space occupied by various character types:

MySQL’s date type

MySQL mainly supports 5 date types. date, time, year, datetime, timestamp.

Date type. The format supports year, month and day. YYYY—MM—DD;

Time type. Minutes and seconds. HH: II: SS.

Datetime is a combination of date and time. That is the year, month and day. Hours, minutes and seconds passed.

timestamp is almost the same as datetime.

But you can’t be too particular about studying. Let’s take a look at the difference between timestamp and datetime.

         represents different value ranges, the value range of datetime is much larger than the value range of timestamp. Timestamp will be finished by 2037.

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

                                                                                      For dates or times of the same timestamp type. Different losses show different results. Using the MySQL command show varialbes like "time_zone" will display the following information.

This SYSTEM means that the system time is used.

When a record containing timestamp data is modified, the timestamp data will be automatically updated to the current date and time of the mysql server.

MySQL binary type

I should not be involved in the binary data type of the MySQL database, at least not yet. Just knowing that mysql can store binary data is enough, I think. Come back to study if necessary in the future. ： 悟 A little perception:

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

(2) The simpler the data type, the better.

(3) Try to use precise decimal types.

(4) Try to use built-in date and time data types instead of using strings to store dates and times.

(5) Try to avoid null fields, and it is recommended to specify fields as not null constraints. This is because it is difficult to optimize queries for columns containing null values ​​in MySQL. Null values ​​will make index statistics and comparison optimization operations more complicated. It is recommended to use 0, a special value or an empty string instead of null.

All articles on my blog are original. Some articles may cite relevant information, but the source has been clearly stated. Feel free to reprint and share, but you must add a link to this article and a copyright statement.

The above is the content of Mysql study notes (1). For more related content, please pay attention to the PHP Chinese website (www.php.cn)!