A few questions about MySQL database design?

Question

MySQL

When writing the database schema with DDL handles, several questions arose, I would like to get a more detailed answer, and not in the form of Y / N.
Here are the actual questions that I would like to get answers to:

1. Do I need to specify DEFAULT NULL, there is a field and so NULLbirthday DATE NULL
   
2. idWhether it is necessary to specify for the field NOT NULLif itauto_increment
   
3. PRIMARY KEYWhether it is necessary to indicate the field withNOT NULL
   
4. Do I need to change the field encoding data BINARYto COLLATE `binary`if the table is in UTF-8
   
5. If the database and tables are in utf8mb4_unicode_cibut there is a field in which only Latin letters are possible, assign an encoding latin1, what else will change besides the loss of Cyrillic (Table size, speed increase ...)
   

Answer 1

1. NULL indicates that the field can be null, and DEFAULT NULL indicates that the default value for this field is NULL. If there is no [NOT NULL | NULL], the default is NULL. If there is no [DEFAULT default_value] in the field description, then DEFAULT NULL is taken by default for NULL, otherwise the default value is not defined.
For example,
That is, if the field is NULL, then DEFAULT NULL can be omitted.
2. If the id field is a PRIMARY KEY, then regardless of whether it is auto-incrementing or not, you cannot specify NULL for it (there will be an error). In this case, if NOT NULL is missing, then it is assumed by default. So, for the primary key NOT NULL, you can not write.
3. see item 2
4. The BINARY type stores binary (binary) strings. For binary data, the concept of encoding is not defined, since the concept of encoding makes sense only for text data. Whether it's possible to change the encoding for a BINARY field or not, I don't know, but it's just pointless.
5. I don't know how it is implemented in MySQL, but for purely theoretical reasons, if the text field cannot contain (based on the data model) characters outside the one-byte encoding, then it is better to set this one-byte encoding for the field. Firstly, this will reduce the space occupied (Russian letters in UTF-8 weigh 2 bytes each), and secondly, if you have this field of the CHAR type, then in a single-byte encoding it will have a fixed size in bytes, which should have a positive effect on performance.