学习sql注入:猜测数据库
What are SQL data types, why do we need them, and how to use them? Today, we’ll try to answer these questions by giving an overview of some most common data types used in SQL Server.
什么是SQL数据类型？为什么需要它们？如何使用它们？ 今天，我们将通过概述SQL Server中使用的一些最常见的数据类型来尝试回答这些问题。
该模型 (The Model)
Before doing anything else, we’ll take one quick look at the model we’re using in this series.
在做其他事情之前，我们将快速浏览一下本系列中使用的模型。
You can notice that each table has columns defined by their name, but also with their type. While the name is pretty obvious, and we’re the one defining it, the thing is little different when it comes to types. We can choose a column type from a list of predefined types or our custom type (again build from predefined types).
您会注意到，每个表都有由其名称和类型定义的列。 虽然名称很明显，而且我们是一个定义它的人，但是在类型方面却几乎没有什么不同。 我们可以从预定义类型或自定义类型的列表中选择列类型（同样从预定义类型构建）。
SQL数据类型–快速介绍 (SQL Data Types – Quick Intro)
Talking about data types is not the hottest thing in programming. They are just one of these things that are here, and we expect it to work. Still, it’s necessary to understand what we have at disposal.
谈论数据类型并不是编程中最热门的事情。 它们只是这里的其中之一，我们希望它能起作用。 尽管如此，还是有必要了解我们所拥有的。
When we’re storing data, we need to simulate “measures & formats” used in the real world. E.g. we want to store height as a decimal number, birth date as a date, ‘this sentence’ as a text, etc. You could always go with an extreme and store everything as text or photo.
当我们存储数据时，我们需要模拟现实世界中使用的“度量和格式”。 例如，我们要将身高存储为十进制数字，将出生日期存储为日期，将“此句子”存储为文本，等等。您总是可以选择极端，将所有内容存储为文本或图片。
Since databases are meant to store data and work with them efficiently, we won’t do that. We’ll have several different SQL data types at our disposal. We won’t only have types to store text or number, but we’ll also go deeper, with types closely describing the nature of that text (how long is it) or number (is it integer or decimal).
由于数据库旨在存储数据并有效地使用它们，因此我们不会这样做。 我们将使用几种不同SQL数据类型。 我们不仅将具有用于存储文本或数字的类型，而且还将更深入地介绍类型，这些类型紧密描述了文本的性质（多长时间）或数字（整数或十进制）。
The main idea behind data types is to simulate the most common types from the real-world. By doing so, the DBMS (database management system), will be able to store them efficiently (less space used, better organized), and that will also lead to improved performance.
数据类型背后的主要思想是模拟现实世界中最常见的类型。 这样，DBMS（数据库管理系统）将能够有效地存储它们（使用的空间更少，组织得更好），并且还可以提高性能。
These types vary between different DBMSs (SQL Server, MySQL, Oracle), but most of them are very similar. We’ll focus on the most important SQL Server/Transact-SQL data types.
这些类型在不同的DBMS（SQL Server，MySQL，Oracle）之间有所不同，但是大多数都非常相似。 我们将重点介绍最重要SQL Server / Transact-SQL数据类型。
文字数据类型 (Textual Data Types)
As you could see in our model, there are 3 most common types of real-world data we need to store: texts, numbers, and dates. Therefore, most DBMS (and programming languages) will support these 3 main groups. In each of these groups, you’ll find more or less standardized SQL data types.
正如您在模型中看到的那样，我们需要存储3种最常见的现实世界数据类型：文本，数字和日期。 因此，大多数DBMS（和编程语言）将支持这3个主要组。 在每个组中，您都会找到或多或少的标准化SQL数据类型。
So, let’s quickly review textual data types first. In the table below, you’ll find Transact-SQL string/textual data types with short descriptions.
因此，让我们首先快速查看文本数据类型。 在下表中，您将找到带有简短说明的Transact-SQL字符串/文本数据类型。
The most important are:
最重要的是：
- char(n) – where n is replaced by the maximum number of characters we expect in any string. If that number is exceeded, we won’t be able to store the complete string. E.g. char(8) means that we’ll store 8 characters. If the string has less than that, the remaining characters shall be filled with blanks. Therefore, char(n), always has the same length, uses static memory allocation, and works 50% faster than varchar(n) (in an ideal scenario). It’s wise to use char(n) when you’re completely sure about the length of the data you’ll store, e.g. postal codes, account numbers, phone numbers, bank codes, etc. char（n）–其中n被我们期望在任何字符串中的最大字符数替换。 如果超过该数字，我们将无法存储完整的字符串。 例如char（8）表示我们将存储8个字符。 如果字符串少于该数字，则其余字符应用空格填充。 因此，char（n）始终具有相同的长度，使用静态内存分配，并且比varchar（n）快50％（在理想情况下）。 当您完全确定要存储的数据长度（例如邮政编码，帐号，电话号码，银行代码等）时，最好使用char（n）。
- varchar(n) – serves the same purpose as char(n), but the difference here is that the string ends after the last character – we don’t add blanks to use the whole string. E.g. if you use varchar(255) and fill on 15 characters, then only 15 characters are stored. The problem here is that we don’t know the exact length of each string, so everything works slower (still, nothing critical). So, we’ve traded better performance in order to save some disk space varchar（n）–具有与char（n）相同的目的，但是区别在于字符串在最后一个字符之后结束–我们不添加空格来使用整个字符串。 例如，如果您使用varchar（255）并填写15个字符，则仅存储15个字符。 这里的问题是我们不知道每个字符串的确切长度，因此一切工作都较慢（仍然，没有关键要求）。 因此，为了节省一些磁盘空间，我们进行了更好的性能交换
- text – well, if you want to store an immense text, you should use this one 🙂 文本–好吧，如果要存储大量文本，则应使用此文本
数值数据类型 (Numeric Data Types)
The next big group of SQL data types is definitely the one containing numeric data types. Once more, we have the overview given in the table below.
下一个主要SQL数据类型组无疑是包含数字数据类型的组。 再一次，我们在下表中给出了概述。
Still, I want to point to some of the most important types from this group:
我仍然要指出该组中一些最重要的类型：
- int – Whole numbers are the first numbers we learn. We count sheep to fall asleep – 1 sheep, 2 sheep… I want to say that everything starts with them. In most cases, you’ll use int – for primary and foreign keys these shall be unsigned. In other places, you’ll use int when you need to store whole numbers. In case, you want to spare some disc space, you could use tiny, or small int if you’re sure this will do. bigint should really be able to cover all your needs regarding whole numbers int –整数是我们学习的第一个数字。 我们数着绵羊入睡– 1绵羊，2绵羊…我想说，一切都始于它们。 在大多数情况下，您将使用int –对于主键和外键，这些键应为无符号。 在其他地方，需要存储整数时可以使用int。 万一您要保留一些磁盘空间，可以确定可以使用tiny或small int。 bigint应该真正能够满足您有关整数的所有需求
- decimal(p,s) / float(n) / real – Are used to store decimal numbers. We can expect that most numerical values we want to store are actually decimal values – percentage, graphical coordinates, sports results etc. 十进制（p，s）/浮点数（n）/实数–用于存储十进制数字。 我们可以预期，我们要存储的大多数数值实际上都是十进制值-百分比，图形坐标，运动结果等。
- bit – Uses only 1 bit to store value 0 or 1 (NULL if not defined). It serves as a flag telling us if something stands or not. One of the most common usages of this type is to store info if the data in the related record is still valid/active/not deleted 位–仅使用1位存储值0或1（如果未定义，则为NULL）。 它用作标记，告诉我们是否站立或不站立。 这种类型最常见的用法之一是在相关记录中的数据仍然有效/有效/未删除的情况下存储信息
约会时间 (Date & Time)
The last SQL data types group we’ll analyze are date & time types. The overview is given in the table below.
我们将分析的最后一个SQL数据类型组是日期和时间类型。 下表中给出了概述。
I would like to emphasize the following ones:
我想强调以下几点：
- date – In most cases, you’ll need to store date only, e.g. birth date, registration date, etc. 日期–在大多数情况下，您只需要存储日期，例如出生日期，注册日期等。
- datetime – Stores date and time in one attribute datetime –将日期和时间存储在一个属性中
- timestamp – Is not SQL Server standard, but you’ll probably meet it in other DBMSs. It serves the same purpose as datetime, but is UTC-time related 时间戳记-不是SQL Server标准，但您可能会在其他DBMS中遇到它。 它的作用与日期时间相同，但与UTC时间相关
The list of the remaining interesting MS SQL data types is given in the table below.
下表列出了其余有趣的MS SQL数据类型。
那么，您需要使用哪种SQL数据类型？ (So, which SQL Data Type You Need to Use?)
The answer to this question is simple and complex at the same time. In most cases, you’ll know exactly what you need – a whole number is a whole number. Still, there is a lot to play with – combining char and varchar, avoid using text whenever possible. It’s a game where you should guess anything end user would like to insert and have this supported. Of course, with the best possible performance and minimum disk space allocated.
这个问题的答案既简单又复杂。 在大多数情况下，您会确切地知道需要什么-整数就是整数。 尽管如此，还是有很多玩法–结合使用char和varchar，尽可能避免使用文本。 在这款游戏中，您应该猜出最终用户想要插入的任何内容并获得此功能的支持。 当然，要获得最佳性能并分配最少的磁盘空间。
目录 (Table of contents)
学习sql注入:猜测数据库
201
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
