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