MySQL String type data

Source: Internet
Author: User
Tags truncated

String types are data types that store strings in the database, and string types include Char,varchar,text,enum and set.

OK, let's take a look at a few of the types mentioned above.

    • char type and varchar type

Both the char type and the varchar type Specify the maximum length when the table is created, and its basic form is as follows: String type (M). Where the string type parameter specifies whether the data type is a char type or a varchar type, and the M parameter specifies that the maximum length of the string is M. For example, char (4) means that the data type is a char type with a maximum length of 4.

The length of a char type is fixed and is specified when the table is created, and can be any value of 0~~255.

For example, char (100) is the specified char type with a length of 100.

The length of the varchar type is variable, specifying the maximum length when the table is created. When defined, its maximum value can take any value between 0~~65525. After specifying the maximum value of the varchar type, the length can be between o and the maximum length.

For example, varchar (100) has a maximum length of 100, but not every record consumes 100 bytes. Instead, within this maximum range, how much allocation is used, and the actual space occupied by the varchar type is the actual length of the string plus one. In this way, the space of the system can be saved effectively.

Now let's take an example here, we put strings of different lengths into char (5) and varchar (5). Now we compare the stored form in the database with the number of bytes occupied, such as.


about the explanation:

1, the space occupied by char (5) is 5 bytes Regardless of the string we insert. As we have said before, the length of char type is fixed

The number of bytes 2,varchar (5) is based on the actual length plus 1. Because the end identifier of the string takes up one byte. For example, in the fourth row of the table above, varchar still retains the trailing space of the string ' 123 '.

To confirm that the next space is preserved, we will now add the ' * ' character to the data behind it and then actually manipulate the char type and the varchar type in the database. The table statement is as follows:

[SQL]View PlainCopy  
    1. CREATE TABLE ' linkinframe '. ' Test ' (
    2. ' id ' INT not NULL,
    3. ' A ' CHAR (5) NULL,
    4. ' B ' VARCHAR (5) NULL,
    5. PRIMARY KEY (' id '));

Now we insert several data into the database:

[SQL]View PlainCopy 
  1. INSERT into ' linkinframe '. ' Test ' (' id ', ' a ', ' B ') VALUES (' 1 ',' ', ');
  2. INSERT into ' linkinframe '. ' Test ' (' id ', ' a ', ' B ') VALUES (' 2 ',' 1 ',' 1 ');
  3. INSERT into ' linkinframe '. ' Test ' (' id ', ' a ', ' B ') VALUES (' 3 ',' 123 ',' 123 ');
  4. INSERT into ' linkinframe '. ' Test ' (' id ', ' a ', ' B ') VALUES (' 4 ',' 123 ',' 123 ');
  5. INSERT into ' linkinframe '. ' Test ' (' id ', ' a ', ' B ') VALUES (' 5 ',' 12345 ',' 12345 ');
  6. INSERT into ' linkinframe '. ' Test ' (' id ', ' a ', ' B ') VALUES (' 6 ',' 1234567 ',' 1234567 ');

The simulation database data is displayed as follows:


Summary: The above practice proves a conclusion that the varchar type retains the trailing space of ' 123 ', while the space after ' 123 ' in the char type is automatically deleted.


The char and varchar types are the 2 most commonly used string types, and there are 2 special instructions for these 2 types:

1, if the length of the inserted string is already greater than the maximum value that can be inserted, then this time the database will error, said data too long for column.
For example, now that we define 2 char and varchar fields with a length of 5, but the value we insert is ' 123456 ', then the system will block the insertion of this value and error.
The question of the length of the inserted string is greater than the length of the data definition, which is what is said above, and then the use of ORM in the usual code is clearly met with this error.
But in my actual SQL practice, such as the last data inserted in the database above, the database did the interception operation, and no error, but a warning, the console output is as follows:

Data truncated for column ' A ' at row 1. Data truncated for column ' B ' at row 1

2, we define the length of the data defined when the length of the length of the definition is the maximum length of the string, for example, I define a name field, varchar (6), then the field can hold up to 6 characters.
This is not confusing with Oracle's VARCHAR2-type fields. The string type in Oracle 2 bytes represents a Chinese character.
For example, or the above statement, now insert a string of data into it, a string beyond the definition of the length of 5, one does not exceed the result of the string is truncated.

[SQL]View PlainCopy 
    1. INSERT into ' linkinframe '. ' Test ' (' id ', ' a ', ' B ') VALUES (' 6 ',' I love you ',' I love You I love you ');

The database now appears as follows:




    • Text type

The text type is a special type of string. Text can only hold character data, such as news content.

The text type includes Tinytext,text,mediumtext,longtext. You will now compare the allowable lengths and storage spaces of the 4 text types, such as:


Text Type Summary:

This type of string is not used in practice, and is generally used to store a larger text, such as an article, a piece of news.

As you can see, the difference between the various text types is that the allowed lengths and storage spaces are different. Therefore, in these types of text, according to the needs of the choice of skills to meet the needs of the type can save space.

    • Enum type

The enum type is also known as an enumeration type, and when you create a table, the range of values for the enum type is specified as a list. Its basic form is as follows: the property name Enum (' Value 1 ', ' Value 2 ',..., ' value n '), where the property name parameter specifies the name of the field, the ' value n ' parameter represents the nth value in the list, and the spaces at the end of these values are deleted directly by the system. Attention:

The value of the 1,enum type can only go to one element in the list, with a maximum of 65,535 values in its list of values. Each value in the list has a sequential number, which is the number that is stored in MySQL, not the value in the list.
2, if the enum type is prefixed with a NOT NULL property, the default value is the first element in the list of values. If the not NULL property is not added, the enum type allows NULL to be inserted, and NULL is the default value.

OK, now let's actually simulate this type of data in MySQL.

The table statement is as follows:

[SQL]View PlainCopy 
    1. CREATE TABLE ' linkinframe '. ' Test ' (
    2. ' id ' INT not NULL,
    3. ' A ' ENUM (' man ', ' woman ') NULL,
    4. PRIMARY KEY (' id '));

Now we insert several data into the database:

[SQL]View PlainCopy  
  1. INSERT into ' linkinframe '. ' Test ' (' IDs ', ' a ') VALUES (' 1 ', ' man ');
  2. INSERT into ' linkinframe '. ' Test ' (' id ', ' a ') VALUES (' 2 ', ' woman ');
  3. INSERT into ' linkinframe '. ' Test ' (' id ', ' a ') VALUES (' 3 ', null);
  4. INSERT into ' linkinframe '. ' Test ' (' id ', ' a ') VALUES (' 4 ', ' 2 ');
  5. INSERT into ' linkinframe '. ' Test ' (' id ', ' a ') VALUES (' 5 ', ' 1 ');
  6. INSERT into ' linkinframe '. ' Test ' (' id ', ' a ') VALUES (' 6 ', ' huhu ');

The database now appears as follows:




OK, a summary of the enum types:

1, using the enum type, the number of each enumeration actually stored in the database, not the value in the list, so we can also insert the number of each enumeration directly.
2, in practice, if I do not insert a value above the enumerated value of the NOT NULL, or insert a value casually, the database is stored empty.
3, in real-world storage, if you can select only one value in the list, choose the enum type, and if you need to pick a combination of multiple values in the list, you need to select the set type.

    • Set type

It also says that if you need to pick a combination of multiple values in a list, you will select the set type. When you create a table, the range of values for the set type is specified as a list. Its basic form is as follows: Property name Set (' Value 1 ', ' Value 2 ',...., ' value n '). Where the ' property name ' property specifies the name of the field, the ' value n ' parameter represents the nth value in the list, and the spaces at the end of those values are deleted directly by the system. Its basic form is the same as the enum type. Attention:

The value of the 1,set type can go to an element in the list or to a combination of multiple elements. When you go to multiple elements, separate the elements with commas. The value of a set type can consist of up to 64 elements.

2, as with the enum type, each value in the list has a sequential number, which is actually stored in MySQL instead of the value in the list.

3, when inserting a record, the order of the elements in the Set field is irrelevant, and the database system is automatically displayed in the order defined when it is stored in the MySQL database. OK, now let's actually manipulate the data type of the database:
The table statement is as follows:

[SQL]View PlainCopy 
    1. CREATE TABLE ' linkinframe '. ' Test ' (
    2. ' id ' INT not NULL,
    3. ' A ' SET (' A ', ' B ', ' C ', ' d ') is not NULL,
    4. PRIMARY KEY (' id '));

Insert several statements:

[SQL]View PlainCopy  
  1. INSERT into ' linkinframe '. ' Test ' (' id ', ' a ') VALUES (' 1 ', ' a ');
  2. INSERT into ' linkinframe '. ' Test ' (' id ', ' a ') VALUES (' 2 ', ' C ');
  3. INSERT into ' linkinframe '. ' Test ' (' id ', ' a ') VALUES (' 3 ', ' B ');
  4. INSERT into ' linkinframe '. ' Test ' (' id ', ' a ') VALUES (' 4 ', ' a,b,c,d ');
  5. INSERT into ' linkinframe '. ' Test ' (' id ', ' a ') VALUES (' 5 ', ' a,e ');

The database appears as follows:


a summary of the set types:

1,set types and enum types are useful for values that are discrete in a certain range. The enum type can only take a value in the list of values, and the set type may remove multiple values in the list of values.

2, both types of data are not directly stored in the database, but the numbers in their list are stored in the database.

3, the data said that if you randomly inserted into these 2 types of data, will be error, but my own practice did not error, but insert a null.

4, for set, if you insert multiple values, some of which are legal, some value is not legal, then will only be legally inserted into, illegal do not do processing.

MySQL String type data

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.