User Tools

Site Tools


programming:mysql:mysql

Some Words about MySQL

# mysql -u root -p
Enter password: 
mysql> create database myDatabase;
Query OK, 1 row affected (0.01 sec)
mysql> grant usage on *.* to myUsername@localhost identified by 'myPassword';
Query OK, 0 rows affected (0.10 sec)
mysql> grant all privileges on myDatabase.* to myUsername@localhost;
Query OK, 0 rows affected (0.02 sec)
mysql> exit
Bye

# mysql -u myUsername -p
Enter password: 
mysql> use eatimeclock;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> show grants;

+———————————————————————————————————————+

Grants for myDatabase@localhost

+———————————————————————————————————————+

GRANT USAGE ON *.* TO 'myUsername'@'localhost' IDENTIFIED BY PASSWORD '*XXXXXXX'
GRANT ALL PRIVILEGES ON `myDatabase`.* TO 'myUsername'@'localhost' WITH GRANT OPTION

+———————————————————————————————————————+ 2 rows in set (0.01 sec)

mysql> exit
Bye

Creating Tables

The first column should be:

  • Field: ID_
  • Type: INT or a variation of it!
  • Null: NOT NULL
  • Extra: AUTO_INCREMENT
  • a PRIMARY KEY

example:

 CREATE TABLE `patient` (
`id_patient` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
...//Others instructions
) ENGINE = MYISAM CHARACTER SET latin1 COLLATE latin1_swedish_ci

Types

Q&A

    • Q: MySQL says: “This is not a number”
    • R: VARCHAR needs a “Length/Values”
      `firstname` VARCHAR( 40 ) NOT NULL

Mysql timezone

mysql> SET time_zone = 'Europe/Zurich';

If set time_zone fails with this error:

ERROR 1298 (HY000): Unknown or incorrect time zone: 'Europe/Zurich'

you need to load the time zone info into mysql with a command like this:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

For more info see http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

If this error,

ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_VARIABLES' feature is disabled; see the documentation for 'show_compatibility_56'

mysql>SET GLOBAL show_compatibility_56 = ON;

To keep variable always ON even if you restart the server, create file my.cnf

Read : http://mysql-tools.com/home/1-mysql/125-showcompatibility56-mysql-57-connection-problem.html

mysql> SET time_zone = 'Europe/Zurich';
mysql> SELECT @@session.time_zone;
 
-> Europe/Zurich

Reflexions and tests about storage size

Based on MySQL v5.7.

  1. The lowest storage size for a row is a byte. The following declarations are not stored in one single bit.
    tinyint(1)
    bit(1)
    bit -- when the number is missing, mysql assuming bit(1)
    bool -- is just an alias for tinyint(1)
    boolean -- idem

    It's exactly the same with a ENUM. If you use a enum with 4 diffents values, don't think mysql will use only 2 bits. An ENUM use 1 byte (1 to 255 values), or 2 bytes (256 to 65535 values, but the practical limit is less than 3000).

  2. The optional number between parenthesis after an integer type is called display width.
    tinyint(2)
    int(4)
    bigint(20)

    The display width does not constrain the range of values that can be stored in the column.
    To prove it:

    +-------------+--------------------------+------+-----+---------+----------------+
    | Field       | Type                     | Null | Key | Default | Extra          |
    +-------------+--------------------------+------+-----+---------+----------------+
    | id          | smallint    unsigned     | NO   | PRI | NULL    | auto_increment |
    | value_t_1   | tinyint(1) unsigned      | YES  |     | NULL    |                |
    +-------------+--------------------------+------+-----+---------+----------------+
    insert into test_tiny set value_t_1=123;
    +----+-----------+
    | id | value_t_1 |
    +----+-----------+
    |  1 |       123 |
    +----+-----------+

    But, if you have a

    tinyint (4) unsigned zerofill

    And you put a 3 in the row, you get:

    +----+-----------+
    | id | value_t_1 |
    +----+-----------+
    |  1 |      0003 |
    +----+-----------+
  3. There is no rollover. If you insert 256 in a 8 bits unsigned value, Mysql donesn't store 1. If we try this (in the same table):
    mysql> insert into test_tiny set value_t_1=256;

    MySQL give you an error.

    ERROR 1264 (22003): Out of range value for column 'value_t_1' at row 1
  4. For the primary key:
    tinyint unsigned -- 1 byte, 255 elements
    smallint unsigned -- 2 bytes, 65536 elements
    mediumint unsigned -- 3 bytes, approx. 16 millions elements (10E6)
    int unsigned -- 4 bytes, approx. 4 billions elements (10E9)
    bigint unsigned -- 8 bytes, approx. 9 quintillions elements (10E18)

    For all physical device, it's useless to use bigint. It's reasonable to assert that we don't install more than 16 millions sensors or centrals. And if it will be the case one day, the database will be splitted.
    So, for physical elements, mediumint is enough.
    For sample, the bigint can be justified.

  5. For timestamp:
    int unsigned -- Overflow is on year 2'106
    bigint unsigned -- Overflow is on year 292'277'026'596 (easyControl in a Deathstar?)

    So, mediumint is enough for timestamp.

  6. Double VS smallint:
    A double use 8 bytes, and a mediumint use 4 bytes.
    If you exactly know the range and the precision of the stored value, the good question to ask is: what is better between an integer scaled value (4 bytes, but division implied) and double value (8 bytes).

    Let's try it:
    With 600'000 sample in a table order by sampled (old version). We choose 600'000 values because it's the worst theoretical case: 1 measure per seconde during 7 days. When we displaying more than 7 days, MySQL make an average.
    select temperature from static_sample where sampled < 1473840000;
    600005 rows in set (2.13 sec) -- Average execution time (10 requests made), on my macMini
    select temperature/10000 from static_sample where sampled < 1473840000;
    600005 rows in set (2.25 sec) -- Average execution time (10 requests made), on my macMini

    To summarise: we save 50% in the storage size for the cost of 6% in the request execution time.
    But this test is with only one row. The result is not the same with 2 or more rows.

    Let's try a test closer to the actual reality:
    In practice, actually, the database is sorted by sensor_id. The sensor run a measure all 15 minutes. The best we can do with real data is a test with all sample from a sensor (38000).

    select temperature, warning_max, warning_min, critical_max, critical_min from static_sample where static_Sensor_id=246;
    38543 rows in set (0.085 sec) -- Average execution time (10 requests made), on server7
    select temperature/10000, warning_max/10000, warning_min/10000, critical_max/10000, critical_min/10000 from static_sample where static_Sensor_id=246;
    38543 rows in set (0.115 sec) -- Average execution time (10 requests made), on server7

    To summarise: we save 50% in the storage size for the cost of 36% in the request execution time.

If you want more informations, you can read Integer Types, Numeric type attributes and Data type storage.

programming/mysql/mysql.txt · Last modified: 2017/11/24 16:00 by mferreira