Publi

21 Consultas SQL para administrar fácilmente nuestro WordPress

Muchas veces cuando trabajamos con WordPress hacemos uso directamente de algún plugin, dentro de la infinidad de ellos que tenemos disponible, lo cual está muy bien pero a veces nos despegamos un poco de los orígenes. MySQL está hecho para usar MySQL (o MariaDB), hace consultas como todos los demás y ¡podemos aprovecharnos de ello! (e ir mucho más rápido).

Antes de nada, he supuesto que el prefijo de las tablas es el que viene por defecto: «wp_«, si no es así, cámbialo por el que tengas.

Aunque está organizado todo en 14 tramos, como véis hay muchas consultas listas para copiar y pegar

Backup de nuestra instalación

Lo primero, como siempre, antes de tocar nada es hacer una copia de seguridad de nuestra base de datos (si es posible de los archivos, nunca está de más). Podemos hacerlo desde PHPMyAdmin, o si tenemos una terminal cerca, podemos utilizar mysqldump:

$ mysqldump -u[usuario] -p[contraseña] [basededatos] > archivo.sql

Si no queremos dejar la contraseña en el termina, no la ponemos y nos la preguntará antes de hacer la copia.

Para restaurar, sencillamente haremos:

$ mysql -u[usuario] -p[contraseña] [basededatos] < archivo.sql

y ante cualquier problema podemos volver al estado anterior fácilmente.

1 – Migraciones indoloras

Cuando migramos un wordpress de un servidor a otro, normalmente no funcionará en la nueva URL, tenemos que cambiar algunas cosas de sitio, y eso lo podemos hacer directamente en base de datos.

1
2
3
4
UPDATE wp_options SET option_value = REPLACE(option_value, 'http://antigua.web', 'http://nueva.web')
  WHERE option_name = 'siteurl';
UPDATE wp_options SET option_value = REPLACE(option_value, 'http://antigua.web', 'http://nueva.web')
  WHERE option_name = 'home';

No es una buena idea hacerlo en la tabla completa, ya que algunos desarrolladores tienen la (mala) costumbre de meter URLs en elementos serializados, por lo que si la nueva url no tiene el mismo número de letras que la antigua, tenemos que aplicar otras técnicas para poder realizar la migración.

Con esto cambiamos la url de la página. Ahora vamos a echarle un ojo a los permalinks:

1
UPDATE wp_posts SET guid = REPLACE(guid, 'http://antigua.web', 'http://nueva.web');

Ahora, en el contenido de los posts:

1
UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://antigua.web', 'http://nueva.web');

Por último en los metadatos:

1
UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, 'http://antigua.web', 'http://nueva.web');

2 – Listar los comentarios del último año

1
2
3
4
SELECT * FROM wp_comments
   WHERE comment_date>='2014-01-01' AND comment_date<'2015-01-01'
      AND comment_approved=1
      AND comment_type<>'trackback' AND comment_type<>'pingback';

En este caso, estamos pidiendo los comentarios desde el 1 de Enero del 2014 (año-mes-día) hasta justo antes del 1 de Enero de 2015, eso sí, que estén aprobados y no sean trackbacks ni pingbacks.

Del mismo modo podemos listar sólo los comentarios que no son nuestros:

1
2
3
4
5
SELECT * FROM wp_comments
   WHERE comment_date>='2014-01-01' AND comment_date<'2015-01-01'
      AND comment_approved=1
      AND comment_type<>'trackback' AND comment_type<>'pingback'
      AND comment_author_email<>'mi@correo.ext';

O por ejemplo, obtener un listado de lo más comentado desde el 2014

1
2
3
4
5
6
7
8
9
10
11
12
SELECT CASE WHEN LENGTH(wpp.post_title) > 55
       THEN CONCAT(SUBSTRING(wpp.post_title, 1, 55), '...')
       ELSE wpp.post_title END AS title, wpp.post_date, `comment_post_ID`,
   COUNT(*) AS amount
   FROM `wp_comments` wpc
      JOIN wp_posts wpp ON wpc.comment_post_id=wpp.ID
   WHERE `comment_approved` = '1'
      AND comment_date>='2014-01-01'
      AND comment_type<>'pingback'
      AND comment_type<>'trackback'
   GROUP BY `comment_post_ID`
   ORDER BY amount DESC;

Con esta consulta recibimos un listado con los títulos de los posts (recortados, porque hay algunos muy largos), la fecha de publicación y el número de comentarios recibidos desde la fecha especificada.

3 – Posts escritos en el último año

1
SELECT COUNT(*) FROM wp_posts WHERE post_date>='2014-01-01' AND post_date<'2015-01-01';

Si queremos más información, podemos seleccionar información sobre esos posts para verlos más detenidamente:

1
2
3
4
SELECT post_name, post_title
   FROM wp_posts wpp
   WHERE post_date>='2014-01-01' AND post_date<'2015-01-01'
   AND post_status='publish';

4 – Borrar pingbacks

A veces los posts se nos llenan de pingbacks y puede que se descontrole. Podemos hacer:

1
DELETE FROM wp_comments WHERE comment_type='pingback';

Si por ejemplo quieres borrar los pingbacks de una serie de posts (o de uno solo):

1
2
DELETE FROM wp_comments WHERE Comment_type='pingback'
   AND comment_post_ID IN (IdPost1, IdPost2, ...)

5 – Listar las IPs desde las que te llega SPAM

Es muy útil si queremos añadir IPs a listas negras por el número de comentarios de SPAM que nos llegan de ellas.

1
SELECT comment_author_ip, COUNT(*) AS times FROM wp_comments pc GROUP BY comment_author_ip HAVING times>5;

Con esta consulta vemos las IPs únicas que nos han mandado comentarios, junto con el número de veces que han comentado. También, esta consulta la hacemos siempre que haya más de 5 comentarios de SPAM (ya que puede haber muchas que sólo hayan enviado 1 ó 2 y a lo mejor nos dificultan el trabajo más que ayudar).

6 – Listar los posts desde los que entra más SPAM

Muy parecida a una consulta anterior (para ver los posts más comentados).

1
2
3
4
5
6
7
8
9
SELECT CASE WHEN LENGTH(wpp.post_title) > 55
       THEN CONCAT(SUBSTRING(wpp.post_title, 1, 55), '...')
       ELSE wpp.post_title END AS title, wpp.post_date, `comment_post_ID`,
   COUNT(*) AS amount
   FROM `wp_comments` wpc
      JOIN wp_posts wpp ON wpc.comment_post_id=wpp.ID
   WHERE `comment_approved` = 'spam'
   GROUP BY `comment_post_ID`
   ORDER BY amount DESC;

7 – Abrir o cerrar los comentarios de ciertos posts

Si queremos desactivar los comentarios en posts antiguos, podemos hacer:

1
2
UPDATE wp_posts SET comment_status='closed' WHERE post_type = 'post'
   AND post_date<='2014-01-01';

Y de forma parecida podemos jugar con el WHERE para determinar fechas mayores/menores o diferentes tipos de post.
También podemos poner comment_status a ‘opened‘ para permitir de nuevo comentarios.

8 – Borrar comentarios de SPAM

Yo he llegado a tener en algún momento cientos de miles de comentarios de SPAM (nada, a los bots parece que les gusta), llega un momento que la base de datos ocupa mucho y podemos limpiarlo todo de un plumazo:

1
DELETE FROM wp_comments WHERE comment_approved = 'spam';

9 – Identificar tags no utilizados

Si hemos borrado algún post, normalmente los tags y otros metadatos se quedan presentes en base de datos. Y con eso salen en las nubes de tags (aunque con un peso 0 lo único que hacen es hacerla más lenta). También debemos recordar que si actualmente tienes posts programados, estos posts aún no han cogido sus etiquetas, y por tanto, éstas estarán en esta lista. Así que, cuidado cuando vayamos a borrarlas.

1
2
3
4
5
SELECT * FROM wp_terms wt
   INNER JOIN wp_term_taxonomy wtt
      ON wt.term_id=wtt.term_id
   WHERE wtt.taxonomy='post_tag'
      AND wtt.count=0;

10 – Ver listado de personas que han comentado en tu blog

Si queremos obtener un listado único de personas que han comentado (identificadas por su correo electrónico), esta consulta viene muy bien. Además, sacamos información como la URL de la web (si la han dejado). Cuidado con los trackbacks y pingbacks (en esta query nos los quitamos del medio).
Utiliza esta información de forma responsable, no hagas SPAM

1
2
3
4
5
SELECT comment_author, comment_author_email, comment_author_url
    FROM wp_comments
    WHERE comment_type<>'pingback'
       AND comment_type<>'trackback'
    GROUP BY comment_author_email;

11 – Eliminar borradores antiguos

A veces se nos pueden acumular borradores antiguos en la base de datos, pues bien, para deshacernos de ellos:

1
DELETE FROM wp_posts wpp WHERE post_status='draft' AND post_date<'2015-01-01';

He puesto de fecha el 1 de Enero de 2015 (año-mes-día). Cuidado cuando estemos borrando, hemos de comprobar dos veces lo que estamos haciendo!!

12 – Eliminar revisiones

1
2
3
4
5
A lo largo de los años seguro que se nos acumulan cientos de revisiones de posts, y eso sobrecarga la base de datos
DELETE a,b,c FROM wp_posts a
  LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
  LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
  WHERE a.post_type = 'revision'

13 – Insertar un campo personalizado en todos los posts

1
2
3
4
5
INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
  SELECT ID AS post_id, 'MiNuevoCampo' AS meta_key, 'MiNuevoValor' AS meta_value
    FROM wp_posts WHERE ID NOT IN
       (SELECT post_id FROM wp_postmeta WHERE meta_key = 'MiNuevoCampo')
    AND `post_type` = 'post';

Podemos jugar con el post_type y con todas las propiedades que hemos visto antes.

14 – Saber en qué posts tenemos un determinado shortcode

1
2
3
4
5
SELECT post_name,post_status,post_title
   FROM wp_posts
   WHERE post_type = 'post'
      AND post_status NOT LIKE '%draft'
      AND post_content LIKE '%[shortcode%';

La búsqueda del shortcode no es de lo mejor, porque busca «[shortcode» en todos los posts, si el shortcode sabemos seguro que está cerrado, podemos buscarlo cerrado también.

Algo parecido nos vale para encontrar una palabra mal escrita en todos nuestros posts, por ejemplo
Más info:
13 useful WordPress SQL Queries You Wish You new Earlier (He cogido dos queries de esta web que la encontré mientras estaba escribiendo este post)

Actualización 22/12/2015 : Un par de símbolos (> , <) no se veían bien y ya están arreglados.

Indispensable MySQL Queries For Custom Fields In WordPress – De aquí saqué la de la creación de un campo personalizado en todos los posts

También podría interesarte....

There are 14 comments left Ir a comentario

  1. Pingback: 21 Consultas SQL para administrar fácilmente nuestro WordPress | PlanetaLibre /

  2. chistes /
    Usando Google Chrome Google Chrome 40.0.2214.111 en Windows Windows 7

    uff buena info , gracias optimizare mi wp

  3. Laura /
    Usando Google Chrome Google Chrome 43.0.2357.134 en Windows Windows 7

    Hola qué tal, tengo un gran problema que tiene que ver con parte del código que presentas.

    Hice un delete en wp_posts y wp_postmeta, lo hice desde el manager de MySQL, y no metí bien el código pero le di en ejecutar la consulta y al parecer borró todo eso, y ahora en mi sitio web aparece el error:

    Error establishing a database connection

    Quise hacer la restauración de archivos desde el hosting pero marca error en la base de datos, ¿hay modo de recuperar la información? No hice un respaldo, pero tengo entendido hace guardados automáticos y son más de 200 posts, ¿será posible? Espero puedas ayudarme.

    Saludos.

    1. Gaspar Fernández / Post Author
      Usando Mozilla Firefox Mozilla Firefox 39.0 en Ubuntu Linux Ubuntu Linux

      Lo siento muchísimo Laura ! Tiene toda la pinta de que has perdido los datos. A ver, en principio, parece raro que haciendo DELETE en wp_posts y wp_postmeta te puedas cargar la conexión con la base de datos. Aunque esté el código incompleto, no tiene nada que ver una cosa con la otra.
      Intenta mirar desde el manager que utilizas si esas tablas están ahí y si tienen datos. Si es así, haz un backup de la base de datos de inmediato y podrás ponerte a trabajar en arreglarlo porque el problema será de otra cosa.

      La restauración de archivos no hizo nada porque lo que has perdido es la base de datos, y precisamente los guardados automáticos son dentro de la propia base de datos, por lo que si la perdemos, hemos perdido todo.

      También puedes comprobar si en tu hosting hacen copias de seguridad periódicas de los datos, muchos proveedores de hosting lo hacen y podrías estar salvada.

      Por favor, vuelve por aquí para contar cómo va el tema.

      1. Laura /
        Usando Google Chrome Google Chrome 43.0.2357.134 en Windows Windows 7

        Hola Gaspar, fíjate que estuve investigando más del tema y me dicen que con los binary logs puedo salvarme.

        He leído que por default vienen activados, por lo que puedo revertir los cambios que hice, ¿conoces del tema? Ya he leído varios sitios y el código que debe usarse, pero sinceramente no quisiera meterle mano de nuevo…

        Ya he preguntado al proveedor del hosting si tienen respaldos independientemente de que yo no lo haya hecho, aún no me responden.

        Sigo esperando si alguien puede explicarme bien lo de la recuperación a a partir de un tiempo especifico con mysqlbinlog.

        Gracias por la pronta respuesta. 🙂

        1. Gaspar Fernández / Post Author
          Usando Mozilla Firefox Mozilla Firefox 39.0 en Ubuntu Linux Ubuntu Linux

          Yo nunca lo he hecho, sólo he leído sobre ello, así que estamos en las mismas… aunque ya que lo dices un día, a lo mejor me da por experimentar un poco.

          Mucha suerte!

  4. Pingback: Un buen 2015 para el blog. Los posts que han marcado el año y consultas SQL – Poesía Binaria /

  5. Pingback: Cómo crear un plugin para Wordpress. Making of de SimTerm (primera parte) – Poesía Binaria /

Leave a Reply