Hot answers tagged

335

Update in 2019-10-29 As mentions by @Manuel Jordan in comments, utf8mb4_0900_ai_ci is the new default in MySQL 8.0, so the following is now again a better practice: CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; Answer before 2019-10-29 Note: The following is now considered a better practice (see bikeman868's answer): CREATE ...


147

The valid syntax is close to your second try, but you need to escape the column names with backticks not with single quotes: ALTER TABLE `blog` CHANGE COLUMN `read-more` `read_more` VARCHAR(255) NOT NULL;


134

use --no-tablespaces see mysqldump-Documentation mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES if the --single-transaction option is not used, and (as of MySQL 8.0.21) PROCESS if the --no-tablespaces option is not used. Certain options might require other privileges ...


131

Percona's Vadim Tkachenko made this fine Pictorial Representation of InnoDB You definitely need to change the following innodb_buffer_pool_size = 4G innodb_log_buffer_size = 256M innodb_log_file_size = 1G innodb_write_io_threads = 16 innodb_flush_log_at_trx_commit = 0 Why these settings ? innodb_buffer_pool_size will cache frequently read data ...


119

A very good thread on this subject is to be found here and also here. The definitive guide for MySQL is, of course, the documentation, to be found here. In the SQL 2003 standard there are 5 different referential actions: CASCADE RESTRICT NO ACTION SET NULL SET DEFAULT To answer the question: CASCADE ON DELETE CASCADE means that if the parent record is ...


104

select * from information_schema.user_privileges; EDIT: As mentioned by Shlomi Noach: It does not list database-specific, table-specific, column-specific, routine-specific privileges. Therefore, the grant GRANT SELECT ON mydb.* TO myuser@localhost does not show in information_schema.user_privileges. The common_schema solution presented above ...


88

What would address your question is the subject JOIN DECOMPOSITION. According to Page 209 of the Book You can decompose a join by running multiple single-table queries instead of a multitable join, and then performing the join in the application. For example, instead of this single query: SELECT * FROM tag JOIN tag_post ON tag_post.tag_id = tag.id JOIN post ...


86

For the record SELECT * FROM mytable WHERE id IN (1,2,3,4) ORDER BY FIELD(id,3,2,1,4); should work as well because you do not have to order the list in the WHERE clause As for how it works, FIELD() is a function that returns the index position of a comma-delimited list if the value you are searching for exists. IF id = 1, then FIELD(id,3,2,1,4) returns ...


76

You should use: CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Note that utf8_general_ci is no longer recommended best practice. See the related Q & A: What's the difference between utf8_general_ci and utf8_unicode_ci on Stack Overflow.


74

Firstly, find out your FOREIGN KEY constraint name in this way: SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, -- <<-- the one you want! REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'My_Table'; You can also add (to the WHERE clause) if you have more than one ...


71

I previously addressed this issue in: Cannot open table mysql/innodb_index_stats These tables are created for you when you install MySQL 5.6. However, upgrading from MySQL 5.5 does not invoke the creation of these tables. Here are the scripts to create them manually: innodb_index_stats USE mysql; CREATE TABLE `innodb_index_stats` ( `database_name` ...


66

tl;dr Breaking change introduced in minor MySQL update, use --no-tablespaces option in mysqldump from now on (recommended) or add the global PROCESS privilege to the user running the command. I experienced the same issue on some of my machines. Not all at once, not on all commands, not on all users. Why not all at once? Turns out this is a breaking change ...


65

This is an old question, but it came up in a search while I was trying to jog my memory about the correct option name and so now that I've figured it out I'm adding the details here. The key part of the question is: I mean in an included file like /etc/mysql/conf.d/myCustomFile.cnf You can do this from an included option file using the option skip-log-bin. ...


63

Given the table description, I see 66 bytes per row of data 4 bytes per row for the primary key 7 bytes per row for country code index 3 bytes for the country 4 bytes for Clustered Key attached to the country code Total of 77 bytes of data and keys This does not factoring housekeeping for BTREEs or Tablespace Fragmentation For a million rows, that would ...


61

Open terminal and edit /etc/mysql/my.cnf sudo nano /etc/mysql/my.cnf Underneath the [mysqld] section.add: lower_case_table_names = 1 Restart mysql sudo /etc/init.d/mysql restart Then check it here: mysqladmin -u root -p variables


61

Based on this blog post http://ilikekillnerds.com/2014/08/fixing-sequel-pro-sql-encoding-error-for-imported-sql-files/ When importing your database dump, you need to select Western (Mac OS Roman) as the encoding format for the file to import without issue.


59

To filter here means to apply a condition on a set of rows that were selected by a type-search as potential rows, and to only keep rows that fulfill the condition: MySQL will first try to use an index, e.g. do a range scan on your table a using the search-key. It estimates to get 174 rows out of using that index, which is the number in rows. This step is ...


56

This answer speeded up everything a lot: https://stackoverflow.com/a/2167641/292408 I simply SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; at the beginning, and COMMIT; SET unique_checks=1; SET foreign_key_checks=1; at the end. Now it took 3 minutes. (Courtesy of @andreasemer via twitter)


54

Use a compound primary key: CREATE TABLE yourtable ( employeeid INT, blahblah VARCHAR(255), blahblah2 VARCHAR(255), recordmonth DATE, recordyear DATE, PRIMARY KEY (employeeid, recordmonth, recordyear) ) And if your table already exists, drop the old primary key: ALTER TABLE yourtable DROP PRIMARY KEY; And ...


53

No, there is no difference in efficiency between (NOT) EXISTS (SELECT 1 ...) and (NOT) EXISTS (SELECT * ...) in all major DBMS. I've often seen (NOT) EXISTS (SELECT NULL ...) being used as well. In some you can even write (NOT) EXISTS (SELECT 1/0 ...) and the result is the same - without any (division by zero) error, which proves that the expression there ...


53

This solution will generate and then run queries needed to convert databases, tables and columns. It converts all columns of the type varchar, text, tinytext, mediumtext, longtext, char. You should always backup your database in case something goes wrong. Copy the following query into gen_queries.sql, replacing the 4 occurrences of YOUR_DATABASE_NAME with ...


45

TWO TABLES IN THE CURRENT DATABASE If you want to know if two tables are different, run this SELECT IF(COUNT(1)>0,'Differences','No Differences') Comparison FROM ( SELECT column_name,ordinal_position, data_type,column_type,COUNT(1) rowcount FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name IN ('...


45

This is a good question. You have several solutions but your table is quite big so none will be without pain :) You have three solutions to "shrink" InnoDB tables: 1. OPTIMIZE TABLE You can use OPTIMIZE TABLE as you mentionned it but you should care about the innodb_file_per_table variable : mysql> show variables like "innodb_file_per_table"; +------...


44

MySQL and PostgreSQL are quite difference performance-wise. InnoDB and PostgreSQL tables are optimized for different sorts of queries. Understanding these differences is important to understanding how to get good performance out of either. As an example, let's look at the most obvious difference. PostgreSQL vs MySQL/InnoDB Table Structure and What This ...


44

DROP TABLE IF EXISTS test_comments; Query OK, 0 rows affected (0.08 sec) CREATE TABLE test_comments (ID INT, name CHAR(30)) COMMENT 'Hello World'; Query OK, 0 rows affected (0.22 sec) Check you comments in table structure show create table test_comments\G *************************** 1. row *************************** Table: test_comments Create ...


44

I have something similar with using SELECT IF() statement in MySQL if you are trying not to have procedures: select if ( exists( select distinct index_name from information_schema.statistics where table_schema = 'schema_db_name' and table_name = 'tab_name' and index_name like 'index_1' ) ,'select ''index index_1 exists'' ...


44

I see at least two ways of accomplishing this. The first approach is to not grant DELETE and UPDATE privileges on these write-once tables, or, for that matter, any privileges apart from INSERT and SELECT, thus only allowing users to insert into or select from them. This option has no performance overhead, as the privilege check is a part of any statement ...


43

In a politically correct sense, what you just asked for is impossible. Why ? The SUPER privilege is a global privilege, not a database level privilege. When you created the user with grant all privileges on db1.* to user1@'%' with grant option; you populated the table mysql.user with user='user1' and host='%'. All other columns (global privileges) were ...


42

You need to collect all the database names into a space delimited list. Use that for mysqldump MYSQL_USER=root MYSQL_PASS=rootpassword MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}" # # Collect all database names except for # mysql, information_schema, and performance_schema # SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN" ...


42

This is a very common "exam/interview question". I will answer as good as I can: In the standard row formats for InnoDB and MyISAM (dynamic/compact) a VARCHAR(50) and a VARCHAR(255) will store the string text in the same way- 1 byte for the length and the actual string with between 1 and 4 bytes per character (depending on the encoding and the actual ...


Only top voted, non community-wiki answers of a minimum length are eligible