From the documentation (MySQL 8) : Type | Maximum length ———–+————————————- TINYTEXT | 255 (2 8−1) bytes TEXT | 65,535 (216−1) bytes = 64 KiB MEDIUMTEXT | 16,777,215 (224−1) bytes = 16 MiB LONGTEXT | 4,294,967,295 (232−1) bytes = 4 GiB Note that the number of characters that can be stored in your column will … Read more
I would recommend using INSERT…ON DUPLICATE KEY UPDATE. If you use INSERT IGNORE, then the row won’t actually be inserted if it results in a duplicate key. But the statement won’t generate an error. It generates a warning instead. These cases include: Inserting a duplicate key in columns with PRIMARY KEY or UNIQUE constraints. Inserting … Read more
To get all tables with columns columnA or ColumnB in the database YourDatabase: SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN (‘columnA’,’ColumnB’) AND TABLE_SCHEMA=’YourDatabase’;
To add a unique constraint, you need to use two components: ALTER TABLE – to change the table schema and, ADD UNIQUE – to add the unique constraint. You then can define your new unique key with the format ‘name'(‘column1’, ‘column2’…) So for your particular issue, you could use this command: ALTER TABLE `votes` ADD … Read more
IMPORTANT EDIT: It is now possible to achieve this with DATETIME fields since MySQL 5.6.5, take a look at the other post below… Previous versions can’t do that with DATETIME… But you can do it with TIMESTAMP: mysql> create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP); Query OK, 0 rows affected (0.00 sec) mysql> … Read more
Use INSERT IGNORE INTO table. There’s also INSERT … ON DUPLICATE KEY UPDATE syntax, and you can find explanations in 13.2.6.2 INSERT … ON DUPLICATE KEY UPDATE Statement. Post from bogdan.org.ua according to Google’s webcache: 18th October 2007 To start: as of the latest MySQL, syntax presented in the title is not possible. But there … Read more
You can use this query to show the size of a table (although you need to substitute the variables first): SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES WHERE table_schema = “$DB_NAME” AND table_name = “$TABLE_NAME”; or this query to list the size of every table … Read more
Use INSERT … ON DUPLICATE KEY UPDATE QUERY: INSERT INTO table (id, name, age) VALUES(1, “A”, 19) ON DUPLICATE KEY UPDATE name=”A”, age=19
It looks like your MySql session has the safe-updates option set. This means that you can’t update or delete records without specifying a key (ex. primary key) in the where clause. Try: SET SQL_SAFE_UPDATES = 0; Or you can modify your query to follow the rule (use primary key in where clause).
For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database: RENAME TABLE old_db.table TO new_db.table; You will need to adjust the permissions after that. For scripting in a shell, you can use either of the following: mysql -u username -ppassword old_db -sNe ‘show … Read more