Exportar consultas SQL a CSV con MySQL

Al exportar registros en formato csv por lo general utilizamos PHP y vamos recorriendo los registros
Una manera fácil y rápida para generar archivos csv de nuestros registros en la base de datos las podemos hacer con una consulta sencilla desde MySQL.
[sql]
SELECT id,nombre,email,direccion
FROM usuarios
INTO OUTFILE ‘/rp/usuarios.csv’
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘n’
[/sql]
donde:

  • INTO OUTFILE : directorio y archivo donde se guardara.
  • FIELDS TERMINATED BY : separador de cada campo.
  • ENCLOSED BY : delimitación de campo.
  • LINES TERMINATED BY : separador de fila.

Este simple script puede hacer backups directamente, si utilizas PHP tal vez te interese generar archivos CSV con PHP, con esto puedes personalizar mejor el modo de exportación de registros ya que PHP recorrerá cada campo para generarlo.
vía: Tech Recipes

10 consejos SQL para acelerar tu Base de datos

La mayoría de sitios en la Web depende de una o varias Bases de datos, ya sean para consultar, ingresar, borrar registros. Y en todas es necesaria la optimización para el aceleramiento y ahorrar recursos de nuestro Servidor, este articulo trae 10 consejos muy útiles para optimizar y acelerar nuestras bases de datos.

1. Optimizar las consultas con el Cache de Consultas SQL

Utilizando la función date de PHP podemos consultar en el cache de la base de datos:

$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

2. No olvidar agregar índices a los campos

Esto acelerara considerablemente sus consultas a la base de datos, para ver si olvido poner índice en algunos campos puede utilizar EXPLAIN, colocándolo delante de la consulta SQL.

EXPLAIN SELECT ...
FROM usuarios u
JOIN comentarios c ON u.id=c.usuario_id

3. Utilizar LIMIT 1 para obtener una única fila

Cuando sabemos que nuestra consulta nos devolverá solo un registro, por ejemplo al consultar por un id de usuario, es recomendable utilizar LIMIT 1, esto hará que cuando el registro sea encontrado, ya no recorrerá las demás filas para comparar.

$rs = mysql_query("SELECT 1 FROM user WHERE id = '1' LIMIT 1");

4. Agregar índice a los campos de búsqueda

Al tener en una tabla, campos con los que hacemos búsqueda, es bueno que esos campos sean índices para acelerar la consulta.

5. Utilizar el mismo tipo de columnas para los JOIN

Cuando relacionamos tablas tenemos que agregar índices, luego de esto asegurarnos que los dos campos tengan el mismo tipo de dato (DECIMAL, INT, etc.). Ya que esto afectara a procesos y operaciones internas de MySQL.

 $r = mysql_query("SELECT company_name FROM users
     LEFT JOIN companies ON (users.state = companies.state)
     WHERE users.id = $user_id");

6. No utilizar el ORDER BY RAND()

Si queremos sacar un registro de una tabla aleatoriamente, es mejor sacar el valor por código PHP, ya que al utilizar RAND() en MySQL estaremos llamando a la función por cada registro en la tabla para obtener un resultado.

// mal
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// bien
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

7. Evitar utilizar el SELECT *

Traer toda la data es casi siempre innecesario y hace procesar más al motor de base de datos, es muy recomendable cambiar el habitual * por los campos necesarios.

// mal
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
// bien
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";

8. Utilizar NOT NULL

A menos que tengas buenas razones para poner NULL a un campo, trata siempre de evitar esto y colocarlas con NOT NULL, ya que este tipo de columnas (NULL) requieren de un espacio adicional y de una complejidad interna en las sentencias de comparación

9. Guardar direcciones IP en campos UNSIGNED INT

Muchos de los desarrolladores (me incluyo) guardamos las direcciones IP en un campo VARCHAR sin saber que las podemos almacenar en campos INT que tiene solo 4 bytes de espacio, tenemos que asegurarnos que el campo sea UNSIGNED INT para aceptar valores más altos, ya que la dirección IP utiliza el rango de 32 bit.
Para transformar las IP a campo numérico y viceversa podemos utilizar funciones MySQL y PHP.

//mysql
$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";
//php
$ip_num = ip2long($_SERVER['REMOTE_ADDR']);
$ip_normal = long2ip($row['ip_address']);

10. Evitar ejecutar consultas dentro de bucles

Un claro ejemplo es al actualizar una serie de filas en la BD, esto se podría resumir en una sola consulta para optimizar recursos en nuestro servidor.
PHP

foreach  ($display_order  as $id => $ordinal) {
    $sql = "UPDATE categories SET display_order = $ordinal WHERE id = $id";
    mysql_query($sql);
}

MySQL

UPDATE  categories
    SET display_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END
WHERE id IN (1,2,3)

Post Original: Nettuts+

zp8497586rq

jfdghjhthit45

SQL Para Principiantes: Base de Datos

Siguiendo con los tutoriales de SQL basico, esta vez trataremos funciones y opciones que tenemos para trabajar con bases de datos.

CREATE DATABASE: Creando una Base de datos:

Este script será el primero que ejecutemos, ya que crea la base de datos, para esto debemos pasar como parámetro el nombre que asignaremos a nuestra BD.

CREATE DATABASE DB_DEMO;

Mostrándolo en la consola de comandos, si no tuvimos ningún error al momento de ejecutarlo, los errores comunes que debemos evitar son:
– Olvidar colocar el signo “punto y coma” (;) al final de una consulta.
– Crear una BD ya existente.
– Poner comillas al nombre de la BD.
Tomando en cuenta esto, nos debería salir un mensaje: “Query OK, 1 row affected” que nos dice que la consulta se ejecuto normalmente.

Nota: Las consultas SQL son “case insensitive”, quiere decir que sí codificamos en minúsculas o mayúsculas es igual.

Character Set and Collation: Elegir el tipo de codificación

Esta opción que brinda no solo MySQL, no permite elegir el tipo de codificación de los datos a ingresar.

CREATE DATABASE my_first_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Para detallar sobre este tema, pueden leer más en Character Sets and Collations in General del sitio oficial de MySQL.

SHOW DATABASES: Listando las bases de datos.

Este script nos ayudara a listar todas las bases de datos creadas hasta el momento,

SHOW DATABASES

DROP DATABASE: Eliminando bases de datos.

Esta opción se debe utilizar con mucho cuidado, previamente siempre es mejor realizar un backup, ya si al ejecutarlo no habrá vuelta atrás.

DROP DATABASE DB_DEMO;

USE: Seleccionando una base de datos

Esta secuencia SQL nos cambia de bases de datos para poder utilizarla y crear tablas (no requiere el “punto y coma al final”).

USE DB_DEMO
HTTP://WRITINGNARRATIVEESSAY.COM
zp8497586rq

SQL Para Principiantes: Introducción

QL (Structured Query Language) es un lenguaje diseñado trabajar con Sistemas de Administración de bases de datos relacionales (RDBMS), entre estos tenemos los siguientes: MySQL, SQL Server, Postgress, Oracle, Sqlite.
Este tutorial esta dirigido a usuarios con conocimientos básicos sobre SQL, aquí trataremos temas como: instalar el servidor de base de datos SQL, conceptos, consultas más usadas, como obtener registros de la base de datos, crear tablas y bases de datos, insertar, modificar y eliminar registros, entre otras rutinas mas usadas en SQL.

Qué necesitamos para empezar?

Para este tutorial utilizaremos MySQL, por ser muy ligero y de código abierto, para poder instalar el paquete completo utilizaremos XAMPP, si utilizamos Linux o Mac hay alternativas.
Este tutorial: Instalando XAMPP, te enseñara paso a paso la instalación para los diferentes tipos de sistemas operativos.

Empecemos

Para conectarnos por consola, tendremos que setear en el path a MySQL, este ejemplo es para Windows:
Haciendo click derecho en MiPC:
> Propiedades > Opciones Avanzadas > Variables de entorno

Ahora abriremos la consola de comandos y escribiremos lo siguiente:

mysql -u [usuario] -p [clave]

Este script nos conectara al servidor MySQL, tenemos que especificar el usuario y clave, si han instalado el paquete XAMPP, el usuario por defecto es root sin clave.

Para una mejor ayuda, podemos instalarnos herramientas para la mejor administración, esta herramientas las podemos comparar en este post: Herramientas para MySQL
En el próximo tutorial trataremos los temas de Creación de Bases de datos.

jfdghjhthit45

zp8497586rq

refinancehomemortgagee.com
zp8497586rq

Excelentes herramientas libres para MySQL

i estamos desarrollando aplicaciones ya sean para escritorio o para Web y estamos utilizando como motor de base de datos a MySQL es ideal que tengamos algunas herramientas para mejorar y agilizar la etapa de modelamiento de base de datos, implementación y administración.
Estas excelentes herramientas para MySQL que nos trae W3Avenue , nos ayudaran a tener una mejor calidad de trabajo en lo que respecta bases de datos, esta lista muestra aplicaciones de escritorio y Web, también por tipos de sistemas operativos.

Aplicaciones multiplataforma

MySQL GUI Tools
Este programa nos trae herramientas para la administración, ejecución de consultas SQL, y herramientas para migración.
Descargar

MySQL Workbench
Es un modelador de base de datos, puedes armar gráficamente tu estructura y te generara automáticamente el script necesario para la creación.
Descargar

MySQL Control Center
Es un programa para la creación y administración de bases de datos MySQL, permite múltiples conexiones a servidores de bases de datos MySQL.
Descargar

Aplicaciones para Mac Os X

Sequel Pro
Es un administrador de base de datos MySQL muy rápido, permite conexiones locales y remotas.
Descargar

yourSQL
Esta aplicación es un administrador y editor de MySQL muy fácil de usar, excelente para usuarios principiantes como para avanzados.
Descargar

Aplicaciones para Windows

SQLyog
Es una aplicación de escritorio para la administración, ejecución de consultas SQL, backups, restores y otras opciones para mejorar el trabajo con MySQL.
Descargar

HeidiSQL
cliente para la administración de base datos MySQL, permite sincronizar datos entre 2 base de datos, manejar privilegios de usuario, importar y exportar data, puedes correr el programa en Linux con Wine
Descargar HeidiSQL | Descargar Wine

Toad for MySQL
Es una herramienta para el desarrollo de base de datos y la administración de ellas, permite diseñar base de datos, administrarla, generar reportes y consultas SQL.
Descargar

MyDB Studio
Esta es una poderosa herramienta para la administración de un servidor de base de datos MySQL. soporta conexión SSH para mayor seguridad.
Descargar

Basados en Web

phpMyAdmin
El más popular administrador Web para MySQL, esta herramienta esta en PHP y viene por defecto en las instalaciones XAMMP, su funcionalidad es muy buena y rápida, tiene características como: realizar consultas SQL, Backups, administración de BD, entre otras.
Descargar

AeroSQL
Es una aplicación Web para la administración de Base de datos MySQL escrita en PHP, entre sus principales características esta que su interfaz esta hecha en Ext.JS.
Descargar

jfdghjhthit45
zp8497586rq

Cache de páginas con PHP

Para muchos sitios Web que utilizan la potencia de PHP, MySQL o alguna otra base de datos, siempre que carga una página realizan consultas a la DB y su debido procesamiento con el lenguaje de programación, en este caso PHP.
Para las páginas con poco cambio de contenidos como blogs por ejemplo, difícilmente editaríamos un Post cada rato, seria de gran utilidad y rendimiento ponerle cache.
Ejemplo de petición

Este grafico nos muestra como se realiza todo el proceso para mostrar el contenido al usuario que esta realizando una petición mediante Internet:

  • Primero se conecta a la base de datos.
  • Luego procesa las noticias para mostrarlas
  • Finalmente lee una plantilla para reemplazar variables y mostrar las noticia.

Para optimizar la rapidez de respuesta del servidor al usuario, se arma el cache con la ultima consulta que realizo el usuario, de este modo se obviarían los pasos que mencionamos anteriormente.
Este grafico muestra el proceso que se realizaría si la página tuviera u archivo cache.

Este script realiza esta función de guardar en cache toda la respuesta en html, para esto se debió crear una carpeta “cache/” con permisos de escritura, donde se almacenaran los archivos.

// iniciar el buffer de salida
ob_start();
//Aqui pones tu codigo HTML y PHP
$cachefile = "cache/home.html";
$fp = fopen($cachefile, 'w');
fwrite($fp, ob_get_contents());
fclose($fp);
ob_end_flush();

Esta función nos guardara el archivo, ahora debemos tener un script que compruebe que existe cache para evitar la interacción con la DB.

$cachefile = "cache/home.html";
if (file_exists($cachefile)) {
	include($cachefile);
	exit;
}

Agregar un tiempo de expiración para las páginas cache.
No es recomendable dejar una pagina con cache de por vida, para esto tenemos que darle un tiempo determinado y que renueve su información con datos más actuales.

$cachetime = 5 * 60;
if (file_exists($cachefile) &&
   (time() - $cachetime < filemtime($cachefile)))
{
      	include($cachefile);
       	echo "n";
       	exit;
}

Esta seria la forma en que podría quedar el proceso para armar y consultar una pagina cache.

$cachefile = "cache/".$reqfilename.".html";
$cachetime = 5 * 60; // 5 minutes
if (file_exists($cachefile) && (time() - $cachetime
< filemtime($cachefile)))
{
    include($cachefile);
    echo "n";
    exit;
}
ob_start();
//.. Your usual PHP script and HTML here ...
$fp = fopen($cachefile, 'w');
fwrite($fp, ob_get_contents());
fclose($fp);
ob_end_flush();

Original de: The UK Web Design Company

Deshabilitar las revisiones de entradas en WordPress

La revisión de entradas en WordPress genera por cada cambio un nuevo registro en nuestra base de datos, esto realmente solo sirve cuando hay varios usuarios manejando contenidos del blog, pero cuando esto no se da hace que nos llenemos de registros totalmente innecesarios.

Para deshabilitar esta funcionalidad en WordPress solo bastara con encontrar el archivo wp-config.php en la carpeta inicial de tu instalación WordPRess y agregar al final este código:

define('WP_POST_REVISIONS', false);

Si nuestro blog cuenta ya con varias entradas y queremos borrar las filas en nuestra tabla, podemos ejecutar la siguiente consulta en la base de datos (por ejemplo desde phpMyAdmin).

DELETE FROM wp_posts WHERE post_type = "revision";

Este script SQL nos borrara todos los registros de la tabla “wp_posts” que tengan el campo “post_type” con el valor “revision”, WordPress pone por defecto este valor a los post revisados
Fuente: WP Thoughts

MySQL to JSON

MySQL to JSON no es mas que una manera de ahorrar codificación del lado del servidor y también ayudar a disminuir el tiempo valioso de respuesta.

La manera mas simple y con mejor rendimiento si se quiere trabajar con JSON seria que la misma bd te devolviera los resultados en dicho formato.

Usando las funciones CONCAT() y GROUP_CONCAT() lograriamos construir el JSON directamente desde la consulta SQL.

Ejemplo

Para este ejemplo construiremos una tabla sencilla de usuarios(para variar)

CREATE TABLE `user`(
	`id`    int not null auto_increment,
        `name`  varchar(100),
        `email` varchar(50),
        PRIMARY KEY(id)
) ENGINE = InnoDB;

Luego insertaremos unos cuantos registros.

INSERT INTO `user` (`name`, `email`) VALUES
('Cesar', 'cesar@tednologia.com'),
('Maria', 'maria@gmail.com'),
('Jose', 'j0s3@hotmail.com'),
('Albert','albertpr@yahoo.com');

Finalmente realizaremos la consulta que nos traera el JSON.

SELECT
    CONCAT( "[",
        GROUP_CONCAT(
            CONCAT("{name:'",`name`,"'"),
            CONCAT(",email:'",`email`,"'}")
        )
    ,"]")
AS json FROM users;

resultado:

[
    {
        name : 'Cesar',
        email: 'cesar@tednologia.com'
    },
    {
        name : 'Maria',
        email: 'maria@gmail.com'
    },
    {
        name : 'Jose',
        email: 'j0s3@hotmail.com'
    },
    {
        name : 'Albert',
        email: 'albertpr@yahoo.com'
    }
]

Triggers en MySQL 5

p>¿Qué son los triggers?

Los Triggers son simplemente procedimientos guardados en la base de datos que se ejecutan antes (BEFORE) y/o después (AFTER) de algún(os) eventos (INSERT, DELETE, UPDATE) sobre la tabla que fue asociada al trigger.

Tienen dos palabras claves, OLD y NEW que simbolizan los valores anteriores o nuevos de la columna.

  • Los INSERT permiten NEW
  • Los DELETE sólo OLD
  • Los UPDATE ambas, NEW y OLD.

Esta es el la estructura de como se declara un trigger:

CREATE TRIGGER [ Nombre_de_Trigger ]
[ Momento ] [ Evento ] ON [ Nombre_de_Tabla ]
FOR EACH ROW
[ Sentencia_SQL ]

Donde:

  • Nombre_de_Trigger: Es el nombre que identificara al trigger
  • Momento: Sera cuando se ejecute ( BEFORE, AFTER )
  • Evento: Proceso el cual llamara al trigger ( INSERT, UPDATE, DELETE )
  • Sentencia_SQL: SQL que se ejecutara al realizar la acción anterior
  • Un ejemplo de trigger seria guardar un log de cambios de datos de un usuario.

    Primero crearemos la tabla `user`

    CREATE TABLE `user`(
    	`id`      int not null auto_increment,
            `name`  varchar(100),
            `email` varchar(50),
            PRIMARY KEY(id)
    ) ENGINE = InnoDB;
    

    Insertaremos un par de registros

    INSERT INTO `user` (`name`, `email`) VALUES
    ('Cesar', 'cesar@tednologia.com'),
    ('Maria', 'maria@gmail.com'),
    ('Jose', 'j0s3@hotmail.com'),
    ('Albert','albertpr@yahoo.com');
    

    Esta sera la tabla de logs que guardara los datos históricos del usuario

    CREATE TABLE log_user
    (
    	`id`          int not null auto_increment,
    	`name`        varchar(100),
    	`email`       varchar(50),
    	`id_user`     int not null,
    	`user`        varchar(40),
    	`date_update` datetime,
    	primary key(id)
    ) ENGINE = InnoDB;
    

    y finalmente un trigger que se disparará cada vez que alguien modifique un dato de la tabla clientes y lo guardará en una tabla junto al nombre del usuario y la fecha.

    Ahora crearemos nuestro trigger llamado “trigger_log_users” que se ejecutara justo despues de actualizar la tabla `user` y guardara los datos anteriores de `user` en la tabla `log_user`

    CREATE TRIGGER trigger_log_users AFTER UPDATE ON `user`
       FOR EACH ROW
       INSERT INTO log_user(`name`, `email`, `id_user`, `user`, `date_update` )
       VALUES (OLD.`name`, OLD.`email`, OLD.`id`, CURRENT_USER(), NOW() );
    

    Recursos