Tag: mysql

Categories
mysql

MySQL data types

  • int: A variable section from 0 to 255 characters long. 
  • tinyint: A string with a maximum length of 255 characters.    
  • largeint: A  string with a  maximum length of 65535 characters.  
  • date: YYYY-MM-DD.
  • smallint mediumint int : -2147483648 to 2147483647 normal 0 to 4294967295 UNSIGNED.  
  • bigint: -9223372036854775808 to 9223372036854775807 normal 0 to 18446744073709551615 UNSIGNED.
  • float: A small number with a floating decimal point.  8 place accuracy, up to 4 bytes.
  • double: A large number with a floating decimal point. 16 place accuracy, up to 8 bytes.
  • decimal: a double stored as a string , allowing for a fixed decimal point.
  • datetime: YYYY-MM-DD HH:MM:SS.
  • timestamp: YYYYMMDDHHMMSS.
  • time:        HH:MM:SS. year
  • char: CHAR( ) A fixed section from 0 to 255 characters long.
  • tinyblob: tinytext
  • blob: A string with a  maximum length of 65535 characters.BLOB stands for Binary Large OBject.
  • mediumblob: A string with a maximum length of 16777215 characters.
  • longblob: A string with a maximum length of 4294967295 characters.
  • longtext enum: Short for ENUMERATION which means that each column may have one of a specified possible values.
  • set: Similar to ENUM except each column may have more than one of the specified possible values. bool binary varbinary
Categories
mysql

mysql timestamps

  • To set the default timestamp:
    • TIMESTAMP DEFAULT ‘2006:09:02 17:38:44′ ON UPDATE CURRENT_TIMESTAMP
Categories
mysql

Federated tables

Tables located in other databases / or servers

Categories
mysql

heap tables

  • High speed temp tables in memory 
  • No Auto_increment   
  • No Text or Blog available   
  • max_heap_table_size   Max size of heap tables
Categories
mysql

mysql security

Security tips for mysql:

  • Make sure there are no users with no password
  • Make sure there are no users without username
  • Make sure there are no users whose host contains the % string (meaning that the user can log in from anywhere in the network or the Internet)
  • Make sure there are as few users as possible who have unrestricted access, ideally only root
Categories
mysql

Create a dump (copy) of a mysql database

mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql

keyword: backup

Categories
mysql

Typical mysql settings

Default mysql port: 3306

Categories
mysql

mysql subqueries

Example:

SELECT name headofstate population FROM Country WHERE population (SELECT MAX(population) FROM Country)

Categories
mysql

mysql trigger

Example:

CREATE TRIGGER `tgr_name` AFTER UPDATE ON `addresses` FOR EACH ROW begin
update products set countryName new.countryName where products.shippingAddressId old.id;
end;

Categories
mysql

Table Candidate key

Unique enough column on a table that can be considered to be the primary key