MySQL has a concept of user-defined variables. They are loosely typed variables that may be initialized somewhere in a session and keep their value until the session ends. They are prepended with an @ sign, like this: @var You can initialize this variable with a SET statement or inside a query: SET @var = 1 … Read more
CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT * FROM table1) From the manual found at http://dev.mysql.com/doc/refman/5.7/en/create-table.html You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current session, and is dropped automatically when the session is closed. This means that two different sessions can use … Read more
If you’re at the MySQL command line mysql> you have to declare the SQL file as source. mysql> source \home\user\Desktop\test.sql;
Try this function – SELECT VERSION(); -> ‘5.7.22-standard’ VERSION() Or for more details use : SHOW VARIABLES LIKE “%version%”; +————————-+——————————————+ | Variable_name | Value | +————————-+——————————————+ | protocol_version | 10 | | version | 5.0.27-standard | | version_comment | MySQL Community Edition – Standard (GPL) | | version_compile_machine | i686 | | version_compile_os | pc-linux-gnu … Read more
You just need to specify that you want to delete the entries from the posts table: DELETE posts FROM posts INNER JOIN projects ON projects.project_id = posts.project_id WHERE projects.client_id = :client_id EDIT: For more information you can see this alternative answer
On some systems, like Ubuntu, MySQL is using the Unix auth_socket plugin by default. Basically it means that: db_users using it, will be “authenticated” by the system user credentials. You can see if your root user is set up like this by doing the following: sudo mysql -u root # I had to use “sudo” … Read more
I once had this problem and solved it by installing mysql-server, so make sure that you have installed the mysql-server, not the mysql-client or something else. That error means the file /var/run/mysqld/mysqld.sock doesn’t exists, if you didn’t install mysql-server, then the file would not exist. So in that case, install it with sudo apt-get install … Read more
That ibdata1 isn’t shrinking is a particularly annoying feature of MySQL. The ibdata1 file can’t actually be shrunk unless you delete all databases, remove the files and reload a dump. But you can configure MySQL so that each table, including its indexes, is stored as a separate file. In that way ibdata1 will not grow … Read more
To see the index for a specific table use SHOW INDEX: SHOW INDEX FROM yourtable; To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA: SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = ‘your_schema’; Removing the where clause will show you all indexes in all schemas.
MySQL 8.0+: You can use the native REGEXP_REPLACE function. Older versions: You can use a user-defined function (UDF) like mysql-udf-regexp.