search
HomeDatabaseMysql Tutorial别在细节上栽跟头--mysql字段类型详解_MySQL

bitsCN.com

别在细节上栽跟头--mysql字段类型详解

 

mysql字段类型intvarchartext

也许你平时不在意,在设计数据库的时候,数字就设成int(10) 字符串就设成varchar(20)或者text

普通情况下是没有问题的,但是若不理解字段类型和长度的含义,总有一天你会在这里栽跟头,

这篇文章,我将主要就int(以及int的扩展类型,如bigint等),varchar,text三种字段类型,一一详解

首先来算笔账

1字节(Byte)=8比特(bit),

就是说1字节能存储的数据量是2^8即2进制位为8位

如果是整形不包括负数那么范围应该是

00000000~11111111(2进制数)

换成10进制数就应该是0~255,如果有负数的话就是-128~127

int

 

从 -2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据(所有数字)。存储大小为 4 个字节。int 的 SQL-92 同义字为 integer。

 

bigint

 

从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据(所有数字)。存储大小为 8 个字节。

 

smallint

 

从 -2^15 (-32,768) 到 2^15 - 1 (32,767) 的整型数据。存储大小为 2 个字节。

 

tinyint

 

从 0 到 255 的整型数据。存储大小为 1 字节。

 

FLOAT [(M, D)] 

说明:小浮点数;单精度(精度小于D O U B L E) 允许的属性:Z E R O F I L L 

取值范围:最小非零值为±1.75494351E -38; 最大非零值为±3 . 4 0 2 8 2 3 4 6 6 E + 3 8 

缺省值:如果列可为NULL,则为NULL;如果列为NOT NULL,则为0 存储需求:4 字节

 

 DOUBLE[(M, D)] 

说明:大浮点数;双精度(比FLOAT更精确) 允许的属性:Z E R O F I L L 

取值范围:最小非零值为±2 . 2 2 5 07 3 8 5 8 5 07 2 0 14 E -308; 最大非零值为±1。7 9 7 6 9 3 13 -4 8 6 2 3 15 7 E + 3 0 8 

缺省值:如果列可为NULL,则为NULL;如果列为NOT NULL,则为0 存储需求:8 字

 

字符串类型

一个字符所需的存储空间是1bit

 VARCHAR(M) 

说明:0 到M 字节长的可变长字符串。M 应该为1到255 之间的一个整数,或者自MySQL3.23 后为0 到255 之间的一个整数。存储时后跟的空格被去掉。存储时,大于M 个字符的串剪断为M 个字符。 允许的属性:B I N A RY 允许的长度:0 到M 字节 

缺省值:如果列可为NULL,则为NULL;如果列为NOT NULL,则为“” 存储需求:值的长度,加上1字节用来记录长度 

比较:不区分大小写(如果具有B I N A RY 属性,则区分大小写)

TEXT 

说明:常规大小的TEXT 值 

允许的属性:除通用属性外无其他属性 

允许的长度:0 到6 5 5 3 5(0 到216-1)字节 

缺省值:如果列可为NULL,则为NULL;如果列为NOT NULL,则为“” 存储需求:值的长度加上用于记录长度的2 个字节 比较:不区分大小写

 

bitsCN.com
Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Explain the InnoDB Buffer Pool and its importance for performance.Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonMySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersLearning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool