[Rails] I learned about database data type types!

Introduction

The data type of the database has a lot of types, which is confusing. I'm still a beginner in programming, so ** "Well, which one should I use in this case?" ** I do a Google search every time, but I couldn't find a good commentary, so I will write an article that also serves as an output! Thank you.

Data type type list

Data type type
integer Integer type(4 bytes)
bigint Integer type(8 bytes)
decimal Fixed-length integer type (highly accurate decimal)
float Number (floating point number)
string String(1 to 255 characters)
text Long string(1 〜 4,294,967,296 characters)
date date(1000-01-01 〜 9999-12-31)
datetime Date and time(1000-01-01 00:00:00.000000 〜 9999-12-31 23:59:59.999999)
time Times of Day(-838:59:59 〜 838:59:59)
timestamp Time stamp('1970-01-01 00:00:01' UTC ~ '2038-01-19 03:14:07' UTC)
binary Binary string type
boolean True and false
primary_key Primary key(Primary key)

Actually, I should explain everything, but I will only cover the parts that I personally care about! For items that are not listed, please try to google for yourself.

What is the difference between integer and bigint?

** integer **: Integer type (4 bytes) ** bigint **: Integer type (8 bytes)

Suddenly I don't understand from the beginning. Apparently, in the past, integer types were only integers, but when it comes to large-scale SNS sites, the number of members alone has become quite large, and integers can no longer handle it. It seems that.

So, starting with Rails 5.1, the id column has changed to bigint. When creating a new application, there is no particular problem, but if you are using integer in an existing application, it seems that an error may occur when repairing the system.

How do you use text and string properly?

In the general classification method, it is often used as follows.

** string ** (1 to 255 characters) ·name ·Street address ·password ** text ** (1 to 4,294,967,296 characters) ·comment ・ Posted text

I think that the situation varies depending on the site to be created, so it is just an example.

What is the difference between timestamp and datetime?

** timestamp **: Timestamp ('1970-01-01 00:00:01' UTC ~ '2038-01-19 03:14:07' UTC) ** datetime **: Date and time (1000-01-01 00: 00: 00.000000 ~ 9999-12-31 23: 59: 59.999999)

Do you know the ** 2038 problem **! If you look closely, you may notice that timestamp is currently only available until 2038 ... I don't know why this happened, but it seems that there is a shift towards using datetime when recording the date and time. The world is likely to be rough in 2038.

What is binary?

In the first place, "binary" is a computer term, and is a data format in which data is represented by "0" and "1". Rails may recognize it as "a file in which data is written with non-textual information."

The main binary files are ・ Audio file · Image file ・ Compressed file And so on. Binary data is not written in human-readable characters. Therefore, you cannot open binary data with a text editor. I don't know how to use it.

What is boolean?

This is also a concept that I don't really understand. In general, it represents a data type that represents two states, such as true and false.

in conclusion

Recognize that there are quite a few types of data types. I learned a lot because I couldn't get to know it unless I put it together in this way.

Recommended Posts

[Rails] I learned about database data type types!
Rails: A little summary about data types
About Java basic data types and reference type memory
A memorandum about table data types and commands (Rails)
What I learned about Kotlin
What I learned from studying Rails
About Rails 6
[Personal memo] I learned a little about modifiers
Summary of what I learned about Spring Boot
About Java data types (especially primitive types) and literals
About Rails routing
Rails database basics
What i learned
[Rails] About ActiveJob ,!
About Rails controller
About RSpec (Rails)
[Rails] Addition of columns / Change of data type / column name
I will write what I learned about docker anyway (second)
I learned about the existence of a gemspec file
I will write what I learned about docker anyway (first)