Big data training and sharing for good programmers mysql data type , Introduction to data types • Data type setting • Column properties • Character set usage of data types • Select the appropriate data type 2, Introduction to data types
  • Four main categories :
   value type
   Character type
   Time type
   Binary type
  • Data type ABC essential factor :
  Appropriate( appropriate )
  Brief( concise )
  Complete( complete )
  • example 1: Column declaration
  CREATE TABLE people ( id INT, first_name CHAR(30), last_name CHAR(30) );
  • example 2: Negative and unknown values are not allowed
  CREATE TABLE people ( id INT UNSIGNED NOT NULL, first_name CHAR(30),
last_name CHAR(30) );3, Numerical data type
  • Considerations when using numeric data types :
   The range of values represented by a data type
   The amount of space required for column values
   Column precision and range ( Floating point number and fixed point number )
  • Classes of numeric data types :
   integer : integer
   Floating point number : decimal
   Fixed point number : Exact value
  BIT: Bit field value
  4, String data type
  • Represents a sequence of alphanumeric characters in a given character set
  • Used to store text or binary data
  • It is implemented in almost every programming language
  • Support character set and collation
  • It belongs to one of the following categories
   text : Real unstructured string data type
   integer : Structured string type
  5, Binary string data type
  • Byte sequence
   8-bit grouping
  • Store binary values , for example :
   Compiled computer programs and Applications
   Image and sound files
  • Class of character binary data type :
   Binary : Fixed and variable length binary strings
  BLOB: Variable length unstructured collection of binary data
  6, Time data type
  7, Column properties
   The category of the column property :
  • numerical value : For numeric data types (BIT except )
  • character string : For non binary string data types
  • routine : For all data types
  8, How to select data type
  • Consider which data types and character sets can minimize storage and disk I/O.
  • Use fixed length data type :
   If all stored string values have the same length
  • Use variable length data types :
   If the stored string values are different
   For multibyte character sets
  • For frequently used characters , Use a multibyte character set that takes less space .
   Use Basic Multilingual plane (Basic Multilingual Plane, BMP) Other than Unicode character set .