What is the correct way to choose column types?

Asked

Viewed 99 times

1

I always see tables as follows. Ex:

CREATE TABLE posts (
    id int(11) NOT NULL,
    titulo varchar(255) NOT NULL,
    autor varchar(255) NOT NULL,
    conteudo text NOT NULL,
    data varchar(11) NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Why this? Putting VARCHAR in everything is safer than putting the type corresponding to the field?

mysql database datetime typing

  • These lines are completely wrong. What it means identificação correspondente ao campo?

  • I just didn’t know how to express myself the name of the right fields, but if you know the database you understand what I’m talking about.

  • 1

    @Dias ñ understood what you want and what your difficulty is. It could be more specific?

  • @cat CREATE TABLE posts ( id int(11) NOT NULL, titulo varchar(255) NOT NULL, autor varchar(255) NOT NULL, conteudo text NOT NULL, ) ENGINE=Innodb DEFAULT CHARSET=utf8;

  • @Days make a tour for you to learn how to use the site and see how to ask.

  • data VARCHAR NOT NULL, (DOUBT = These fields may have Date in the varchar location, it would be safer or date and varchar has the same value?)

  • @Days if you put VARCHAR the field will accept any value, already the type DATE it will only accept values in date format such as: dd-MM-yyyy or yyyy-MM-dd, vc has one more validation. However it will depend on the context and purpose.

  • @Days of a look at specification Mysql for date type.

  • Thank you, I understand, I was VERY pleased with your reply. Thank you Master.

  • @bigown - You can participate in this post, thank you. http://answall.com/questions/172156/como-implantar-um-input-file-para-imagem?noredirect=1#comment354685_172156

  • 3

    The reason for this is simple: ignorance of who did it. Date is date, varchar is varchar. What is stored inside the field has nothing to do with what you see on the screen, so it has the right types. For example, date stored in varchar or char is usually a sign that the person should try another profession, or study more (has exceptions, but almost always the person thinks it is the case and is not).

  • @Days Did any of the answers solve your question? Do you think you can accept one of them? Check out the [tour] how to do this, if you haven’t already. You would help the community by identifying what was the best solution for you. You can accept only one of them. But you can vote on any question or answer you find useful on the entire site (when you have enough score).

Show 7 more comments

2 answers

4

Every type of column in a table should be as suitable as possible for the need.

If you have a date, you probably need to use a type that has date semantics. If you are going to use quantities, you should use a numeric type. If you’re just going to describe information, no matter how it is composed, you’re going to use a text type. And so on.

In fact the varchar is usually preferred because the bulk of information in a database is usually only descriptive.

In the example if using a varchar in place of a date, or something similar, you will have difficulty classifying the data in chronological order, depending on the format, you may have difficulty presenting, it will be an extreme complication to make calculations with that date (calculate maturity, delay days, data range, etc.)

If what you want to know is why people’s codes are usually everything varchar, I would say it’s because today most people who decide to develop software don’t have the slightest notion of what they’re doing. They don’t know what to do, and they linearize everything. As they do not know what to use or when, they use only one thing that "works". One does not even notice the confusion that will cause later.

And worse, often these people end up teaching others to do wrong too. So you should distrust everything you read on the internet. It may seem the right thing in the world and be wrong, after all who is seeking information in general does not know whether it is right.

This type of phenomenon occurs because people try to learn from top to bottom (in general they never get below). It doesn’t work! The correct thing is to learn from the bottom up, as happens in school. Learning a more basic concept, and going from it to more complex ones that depend on the previous concepts.

And they usually keep doing it wrong because of the effect Dunning-Kruger.

As seen on the Internet

  • Thank you, I understand, I was VERY pleased with your reply. Thank you Master.

  • You can participate in this post, thank you. http://answall.com/questions/172156/como-implantar-um-input-file-para-imagem?noredirect=1#comment354685_172156

1

The "correct shape" is the one that corresponds to the purpose you will give the storage. It is recommended to use numeric types for numbers, and varchar for string and text. Understand: If you store everything in varchar and then need to cross-calculate data can have an unpleasant surprise, or an inaccurate response. See that.

  • 1

    Thank you, I understand, I was VERY pleased with your reply. Thank you Master.

  • Mark the answer that helped you so other people can be helped too ;)

Browser other questions tagged mysql database datetime typing

You are not signed in. Login or sign up in order to post.