mysql示例数据库_MySQL数据类型教程和示例

mysql示例数据库

MySQL is a very advanced database server that is used by different types of companies. MySQL is used for different projects and cases in order to store different types of data in different formats. MySQL supports different types of data for storage, query, update, etc.

MySQL是一种非常先进的数据库服务器，供不同类型的公司使用。 MySQL用于不同的项目和案例，以便以不同的格式存储不同类型的数据。 MySQL支持不同类型的数据进行存储，查询，更新等。

MySQL常规数据类型 (MySQL General Data Types)

MySQL database server provides a lot of different types of data and these data types can be categorized like

MySQL数据库服务器提供了许多不同类型的数据，这些数据类型可以像

• Numeric Data Types
  数值数据类型
• Date and Time Data Types
  日期和时间数据类型
• String Data Types
  字符串数据类型
• Spatial Data Types
  空间数据类型

数值数据类型(Numeric Data Types)

Numeric data types are used to store numeric data like integer, floating-point which can be counted or used for calculations. MySQL provides a wide range of numeric data types to fit the user requirements while keeping the performance and space requirement less.

数值数据类型用于存储可以计算或用于计算的数值数据，例如整数，浮点数。 MySQL提供了各种各样的数字数据类型来满足用户需求，同时又降低了性能和空间需求。

INT is one of the most popular types where normal integer values are stored in INT type. INT can hold up to 11 digits where the range is between -2147483648 and 2147483647 if the stored value is signed. If the value is unsigned allowable range is from 0 to 4294967295.

INT是最流行的类型之一，其中普通整数值以INT类型存储。 如果对存储的值进行了签名，则INT最多可以保留11位数字，范围在-2147483648和2147483647之间。 如果该值是无符号的，则允许的范围是0到4294967295。

TINYINT is used to store tiny integer values which can be up to 4 digits. If the tiny integer is signed values between -128 and 127 can be used but it not signed from 0 to 255 can be stored.

TINYINT用于存储微小的整数值，该值最多可以为4位。 如果微小的整数是带符号的，则可以使用-128到127之间的值，但是可以存储0到255之间的无符号值。

SMALLINT is used to store small integer values which can be between -32768 and 32767 for signed integer values. If not signed SMALLINT type can store from 0 to 65535.

SMALLINT用于存储小整数值，对于带符号整数值，小整数值可以在-32768和32767之间。 如果未签名，SMALLINT类型可以存储0到65535。

MEDIUMINTis used to store medium-sized integers between -8388608 to 8388607 for signed medium integers. If a medium integer is not signed from 0 to 16777215 can be stored. Medium integer can store up to 9 digits.

MEDIUMINT用于存储-8388608到8388607之间的中型整数，用于带符号的中型整数。 如果没有整数，则可以存储从0到16777215的整数。 中整数最多可以存储9位数字。

BIGINT is used to store big integers which is between -9223372036854775808 to 9223372036854775807 for signed big integers. If not signed big integer type can store from 0 to 18446744073709551615. Big integer type can store up to 20 digits.

BIGINT用于存储介于-9223372036854775808到922337203685477575807之间的带符号大整数的大整数。 如果未签名，则大整数类型可以存储0到18446744073709551615。大整数类型最多可以存储20个数字。

FLOAT(M,D)is used to store floating-point data types. As floating type numbers contains decimal and floating part. D is used to specify the decimal part of the number where M is total number of digits used. For example FLOAT(12,4) will store 12 digits in total and 4 of them will be used for decimal part and 8 digits will be used for the floating-point part. FLOAT can store 24 digits for decimal and floating-point in total.

FLOAT(M,D)用于存储浮点数据类型。 由于浮点型数字包含小数和浮点部分。 D用于指定数字的小数部分，其中M是所用数字的总数。 例如FLOAT(12,4)总共将存储12位数字，其中4位将用于小数部分，而8位将用于浮点部分。 FLOAT总共可以存储24位小数和浮点数。

DOUBLE(M,D) is used to store double-precision floating-point numbers where they can not be unsigned.M is used to define total digits or display length where D specifies the digit count of the decimal. For example, DOUBLE(18,4) will store 18 digits in total where 4 digits are used for decimal part and 14 digits will be used for the floating-point part.

DOUBLE(M,D)用于存储不能无符号的双精度浮点数。 M用于定义总位数或显示长度，其中D指定小数位数。 例如，DOUBLE(18,4)总共将存储18位数字，其中4位数字用于小数部分，而14位数字用于浮点部分。

DECIMAL(M,D) is used to store floating-point numbers as digits. M specifies the total numbers used and D is decimal part digits count. DECIMAL also called a fixed-point number. For example, DECIMAL(7,2) will store 7 digits in total where 5 of them used for decimal part and 2 of them will store the floating-point part. Which means from -99999.99 to 99999.99 can be stored inside it.

DECIMAL(M,D)用于将浮点数存储为数字。 M指定使用的总数， D指定小数部分位数。 DECIMAL也称为定点数。 例如，DECIMAL(7,2)总共将存储7位数字，其中5位用于小数部分，而2位将存储浮点部分。 这意味着-99999.99至99999.99可以存储在其中。

BIT(M) is used to store binary values. M is used to set a bit value count. BIT(5) can store 5 bit value which can be “01011”.

BIT(M)用于存储二进制值。 M用于设置位值计数。 BIT(5)可以存储5位值，可以是“ 01011”。

日期和时间数据类型 (Date and Time Data Types)

Date and Time data types are used to store the date and time information in different formats and precision.

日期和时间数据类型用于以不同的格式和精度存储日期和时间信息。

DATE type is used to store only the date which contains the year, month and day information. The date type is formatted as YYYY-MM-DD by default but different format specifiers can be used for different presentation types as this will not affect the value. For example the November 20th, 1984 will be stores as 1984-11-20.

DATE类型仅用于存储包含年，月和日信息的日期。 日期类型默认情况下格式为YYYY-MM-DD，但是可以将不同的格式说明符用于不同的演示文稿类型，因为这不会影响该值。 例如，1984年11月20日将存储为1984-11-20。

DATETIME is the most complex date and time format where both date and time information is stored in the same record. The DATETIME type is store as YYYY-MM-DD HH:MM:SS where YYYY is the year, MM is the month, DD is the day, HH is the hour, MM is minute and SS is second information.

DATETIME是最复杂的日期和时间格式，其中日期和时间信息都存储在同一记录中。 DATETIME类型存储为YYYY-MM-DD HH：MM：SS，其中YYYY是年份，MM是月份，DD是日期，HH是小时，MM是分钟，SS是第二信息。

TIMESTAMP is a different date and time specifier format which stores the seconds from January 1st, 1970 into the specified date and time. TIMESTAMP is also called Unix time format which is popular on Unix platforms.

TIMESTAMP是一种不同的日期和时间说明符格式，用于存储从1970年1月1日到指定的日期和时间的秒数。 TIMESTAMP也称为Unix时间格式，在Unix平台上很流行。

TIME is the format used to store only the time information. TIME stores as HH:MM:SS where HH is the hour, MM is the minute and SS is the second part.

TIME是仅用于存储时间信息的格式。 TIME存储为HH：MM：SS，其中HH是小时，MM是分钟，SS是第二部分。

YEAR(M) is a date-time format where only date year information is stored. M can be 2 or 4 where year digits like 84, 1984 can be stored.YEAR can store from 70 to 69 for 2 digits like YEAR(2) and from 1901 to 2155 for 4 digits like YEAR(4).

YEAR(M)是一种日期时间格式，其中仅存储日期年份信息。 M可以是2或4，可以存储84、1984这样的年份数字.YEAR可以存储70至69的2位数字(例如YEAR(2))和1901至2155的4位数字，例如YEAR(4)。

字符串数据类型 (String Data Types)

String data types is another rich type MySQL data type category where different types and size of strings can be stored.

字符串数据类型是另一种丰富MySQL数据类型类别，可以在其中存储不同类型和大小的字符串。

CHAR(M) is a fixed-length character storage data type that can store a maximum of 255 characters. M is used to specify the right-padded with spaces to the specified length. For example, CHAR(20) can store up to 20 characters.

CHAR(M)是固定长度的字符存储数据类型，最多可存储255个字符。 M用于指定右边的空格以指定的长度填充。 例如，CHAR(20)最多可以存储20个字符。

VARCHAR(M) is used to created variable-length character types where a maximum of 255 characters can be stored. For example, VARCHAR(30) can store a maximum 30 characters. The character count is a must.

VARCHAR(M)用于创建可变长度字符类型，最多可存储255个字符。 例如，VARCHAR(30)最多可以存储30个字符。 字符数是必须的。

BLOB or TEXT datatypes are used to store big text and long strings which can be up to 65535 characters. BLOB is the short form of Binary Large Objects and used for store large amounts of binary data like image, executable file, different document formats. Data type TEXT is case sensitive which can be different text comparison operations like equal, bigger, etc. date type BLOB is binary and does not provide case sensitivity.

BLOB或TEXT数据类型用于存储大文本和长字符串，最多可包含65535个字符。 BLOB是Binary Large Objects的缩写，用于存储大量的二进制数据，例如图像，可执行文件，不同的文档格式。 数据类型TEXT是区分大小写的，可以是不同的文本比较操作，例如equal，large等。日期类型BLOB是二进制的，不提供区分大小写。

TINYBLOB or TINYTEXT is used store BLOB and TEXT type data which can be a maximum of 255 characters.

TINYBLOB或TINYTEXT用于存储BLOB和TEXT类型数据，最多可包含255个字符。

MEDIUMBLOG and MEDIUMTEXT are used to store BLOB and TEXT type data which can be a maximum of 16777215 characters.

MEDIUMBLOG和MEDIUMTEXT用于存储BLOB和TEXT类型数据，最多可以为16777215个字符。

LONGBLOB and LONGTEXT are used to store BLOB and TEXT type data which can be a maximum of 4294967295 characters.

LONGBLOB和LONGTEXT用于存储BLOB和TEXT类型数据，最多可为4294967295个字符。

ENUM is used to store enumeration data type which is data is one of the sets of variables or NULL value. For example, ENUM(“A”, “B”, “C”, “D”) are created enum values where one of them like “B” or NULL can be store in the ENUM type data field.

ENUM用于存储枚举数据类型，该数据是数据是变量集或NULL值之一。 例如，创建了ENUM(“ A”，“ B”，“ C”，“ D”)枚举值，其中的一个值如“ B”或NULL可以存储在ENUM类型数据字段中。

空间数据类型 (Spatial Data Types)

OpenGIS is an open platform and consortium which created different date types to represent geographic information. In order to use and store spatial data types, some extensions should be installed for the MySQL database server.

OpenGIS是一个开放的平台和联盟，它创建了不同的日期类型来表示地理信息。 为了使用和存储空间数据类型，应为MySQL数据库服务器安装一些扩展。

翻译自: https://www.poftut.com/mysql-data-types-tutorial-with-examples/

mysql示例数据库