Skip to content

Instantly share code, notes, and snippets.

@ederrafo

ederrafo/mysql-queries.md

Last active March 3, 2020 08:37
Show Gist options
  • Save ederrafo/6cfa6225872d92329771 to your computer and use it in GitHub Desktop.
Save ederrafo/6cfa6225872d92329771 to your computer and use it in GitHub Desktop.
Download ZIP
query, alter tables mysql
Raw
mysql-queries.md

Insert masivo entre 2 tablas de diferentes bd

INSERT INTO test_modulo_cobranzas.accounts 
(id,
 business_unit_id, 
 name,
 country,
 city,
 email,
 phone,
 addres1,
 addres2,
 addres3,
 updated_at)
 SELECT 
 id,
 business_unit_id, 
 name,
 country,
 city,
 email,
 phone,
 addres1,
 addres2,
 addres3,
 updated_at
 FROM costamar_cobranzas.accounts

Select by column timestamp is very simple : timestamp_column >= '2018-03-01'

Create database

   CREATE DATABASE `blogsymfony2` DEFAULT CHARACTER SET 'utf8';

Disabled safe mode

   SET SQL_SAFE_UPDATES = 0;
   UPDATE ...........................

Add Foreign Key to existing table

   ALTER TABLE products ADD categories_id INT;
   ALTER TABLE products ADD CONSTRAINT fk_products_categorie FOREIGN KEY(categories_id) REFERENCES categories(id);

Eliminando una clave foranea

   ALTER TABLE `table`.`seances` 
   DROP FOREIGN KEY `fk_seances_anothertable1`;
   ALTER TABLE `table`.`seances` 
   DROP INDEX `fk_seances_anothertable1_idx` ;

View infomation of table:

SELECT * FROM   information_schema.tables WHERE  TABLE_SCHEMA = 'costamar_travel' AND TABLE_NAME = 'jobs';

Change column email required to not required

ALTER TABLE `costamar`.`jobs` 
CHANGE COLUMN `email` `email` VARCHAR(255) CHARACTER SET 'utf8' NULL ;

How to find all the tables in MySQL with specific column names in them?

SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='yourdatabase' AND TABLE_NAME LIKE '%sess%';

SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('column1', 'column2')
        AND TABLE_SCHEMA='yourdatabase';

Describe Structure

DESCRIBE sessions;

Agregar una nueva tabla a una base de datos, con llave foranea a otra tabla

CREATE TABLE IF NOT EXISTS `database`.`seances` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `session_id` TEXT NOT NULL,
  `AccountID` VARCHAR(30) NULL,
  `ip_visitor` VARCHAR(255) NULL,
  `login` VARCHAR(255) NULL,
  `type_login` VARCHAR(45) NULL DEFAULT 0 COMMENT '0 : unknow\n1 : agencia\n2 : administrador\n3 : administrador usa\n4 : agentes peru\n5 : agentes usa',
  `date_created` TIMESTAMP NULL,
  `date_updated` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_seances_useragency1_idx` (`AccountID` ASC),
  CONSTRAINT `fk_seances_useragency1`
    FOREIGN KEY (`AccountID`)
    REFERENCES `database`.`useragency` (`AccountID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- Si no se agrega esto 'DEFAULT CHARACTER SET = utf8' mysql manda: 'Error Code: 1215. Cannot add foreign key constraint'

=== mysql 5.7 ===

How do you set a default value for a MySQL Datetime column?, In SQL Server it's getdate(), In version 5.6.5, it is possible to set a default value on a datetime column, and even make a column

  mysql> create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
  Query OK, 0 rows affected (0.00 sec)
  mysql> desc test;
  +-------+-------------+------+-----+-------------------+-------+
  | Field | Type        | Null | Key | Default           | Extra |
  +-------+-------------+------+-----+-------------------+-------+
  | str   | varchar(32) | YES  |     | NULL              |       | 
  | ts    | timestamp   | NO   |     | CURRENT_TIMESTAMP |       | 
  +-------+-------------+------+-----+-------------------+-------+
  mysql> CREATE TABLE foo ( `creation_time` DATETIME DEFAULT CURRENT_TIMESTAMP, `modification_time` DATETIME ON UPDATE CURRENT_TIMESTAMP);

/* I got errors due to DateTime columns being populated with zeros during import Solution:*/

  $ which mysqld
    /usr/sbin/mysqld

Use this path

$ /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"
    Default options are read from the following files in the given order:
    /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
$ mysql -u homestead -psecret -e "select @@sql_mode"
    +-------------------------------------------------------------------------------------------------------------------------------------------+
    | @@sql_mode                                                                                                                                |
    +-------------------------------------------------------------------------------------------------------------------------------------------+
    | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
    +-------------------------------------------------------------------------------------------------------------------------------------------+

/* Then, we copy the current string this query produced and remove everything we don’t like. In my case, I needed to get rid of NO_ZERO_IN_DATE, NO_ZERO_DATE and of course ONLY_FULL_GROUP_BY. The newly formed string then looks like this: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION */ We open the configuration file we decided on before (/etc/mysql/my.cnf) and add the following line into the [mysqld] section: [mysqld]

... other stuff will probably be here

sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Restar mysql $ sudo service mysql restart

ref: http://optimize-this.blogspot.pe/2012/04/datetime-default-now-finally-available.html https://www.sitepoint.com/quick-tip-how-to-permanently-change-sql-mode-in-mysql/

=== Upgrade mysql 5.5 to 5.6 ubuntu 14.04 === $ sudo apt-get remove mysql-server $ sudo apt-get purge --assume-yes mysql-server mysql-server-5.5 mysql-server-core-5.5 mysql-client mysql-client-5.5 mysql-client-core-5.5 $ sudo apt-get update $ sudo apt-get autoremove $ sudo apt-get install mysql-server-5.6 mysql-client-5.6 mysql-client-core-5.6

-- Creando una base de datos / Creating a database CREATE SCHEMA test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;

-- Create a table CREATE TABLE IF NOT EXISTS mydb.geolocations ( id INT NOT NULL AUTO_INCREMENT, code TEXT NOT NULL, name VARCHAR(255) NOT NULL, code_iso VARCHAR(45) NULL, phone_format TEXT NOT NULL, mobile_format TEXT NULL, date_created TIMESTAMP NOT NULL, date_updated TIMESTAMP NULL, PRIMARY KEY (id)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci;

-- Creando una tabla con una llave foranea a la misma tabla --- CREATE TABLE IF NOT EXISTS mydb.geolocations ( id INT NOT NULL AUTO_INCREMENT, geolocations_id INT NULL, code TEXT NOT NULL, name VARCHAR(255) NOT NULL, code_iso VARCHAR(45) NULL, phone_format TEXT NOT NULL, mobile_format TEXT NULL, date_created TIMESTAMP NOT NULL, date_updated TIMESTAMP NULL, PRIMARY KEY (id), INDEX fk_geolocations_geolocations_idx (geolocations_id ASC), CONSTRAINT fk_geolocations_geolocations FOREIGN KEY (geolocations_id) REFERENCES mydb.geolocations (id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci;

-- Agrego una columna nueva a una tabla ALTER TABLE products ADD name varchar(255) not null;

-- Agrego una columna despues de la columna id ALTER TABLE products ADD name varchar(255) AFTER id;

-- Agrego una columna al comienzo ALTER TABLE products ADD id varchar(255) FIRST;

src: http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

max_allowed_packet The largest possible packet that can be transmitted to or from a MySQL 5.1 server or client is 1GB. shell> mysql --max_allowed_packet=32M Si insertamos un registro que contiene un campo con informacion demasiado pesada, debemos modificar el max_allowed_packet

=== LOGS ===

  • For mysql < 5.1.29: To enable the query log, put this in /etc/my.cnf in the [mysqld] section log = /path/to/query.log #works for mysql < 5.1.29 Also, to enable it from MySQL console SET general_log = 1;

  • For mysql 5.1.29+ : general_log_file = /path/to/query.log general_log = 1

    Alternately, to turn on logging from MySQL console SET global general_log = 1;

  • UBUNTU: ENABLE MySQL LOGGING In /etc/mysql/my.cnf, under Logging and Replication, uncomment log:

    * Logging and Replication

    Both location gets rotated by the cronjob.

    Be aware that this log type is a performance killer.

    log = /var/log/mysql/mysql.log

    Restart MySQL: $ sudo /etc/init.d/mysql restart

    View queries: $ tail -f /var/log/mysql/mysql.log

    ref: http://jeromejaglale.com/doc/ubuntu/mysql_enable_logging http://www.howtogeek.com/howto/database/monitor-all-sql-queries-in-mysql/

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment