I have a tweet like this one: https://twitter.com/ATD_hashtagger/status/591204518253170689
#myownlittletest 3
When I try to add this to my database, it becomes this:
???????????????????????????????????????????????????????????????????????????????????????????????????????????????? #myownlittletest 1
My table has collation utf8mb4_general_ci
, the same collation is also used for the field (TEXT
) in which it is inserted.
The value is inserted in the column named text
.
I have a very simple query to insert it:
INSERT INTO `posts_twitter` (`hashtagId`, `coordinates`, `created`, `filterlevel`, `postId`, `language`, `profanity`, `retweeted`, `text`, `truncated`, `userId`, `username`, `userFullname`, `media`, `mediaType1`, `media1`, `mediaType2`, `media2`, `mediaType3`, `media3`, `mediaType4`, `media4`) VALUES (:hashtagId, POINT(:coordinatesLat,:coordinatesLon), :created, :filterlevel, :postId, :language, :profanity, :retweeted, :text, :truncated, :userId, :username, :userFullname, COALESCE(:media1,:media2,:media3,:media4), :mediaType1, :media1, :mediaType2, :media2, :mediaType3, :media3, :mediaType4, :media4) ON DUPLICATE KEY UPDATE `hashtagId` = `hashtagId`
The params are binded by PDO:
array(22) {
["coordinatesLat"]=>
NULL
["coordinatesLon"]=>
NULL
["created"]=>
int(1429788536)
["filterlevel"]=>
string(3) "low"
["postId"]=>
int(591202087243014145)
["language"]=>
string(3) "und"
["profanity"]=>
bool(false)
["retweeted"]=>
bool(false)
["text"]=>
string(131) " #myownlittletest 1"
["truncated"]=>
bool(false)
["userId"]=>
int(2805078403)
["username"]=>
string(14) "ATD_hashtagger"
["userFullname"]=>
string(18) "Hashtag aggregator"
["media1"]=>
NULL
["mediaType1"]=>
NULL
["media2"]=>
NULL
["mediaType2"]=>
NULL
["media3"]=>
NULL
["mediaType3"]=>
NULL
["media4"]=>
NULL
["mediaType4"]=>
NULL
["hashtagId"]=>
int(3)
}
My table is created using the following query:
CREATE TABLE IF NOT EXISTS `local_sysDB`.`posts_twitter` (
`hashtagId` INT NOT NULL,
`postId` CHAR(20) NOT NULL,
`coordinates` POINT NULL,
`created` INT(20) UNSIGNED NOT NULL,
`filterlevel` VARCHAR(45) NOT NULL,
`language` CHAR(11) NOT NULL,
`profanity` TINYINT(1) NOT NULL,
`retweeted` TINYINT(1) NOT NULL,
`text` CHAR(240) NOT NULL,
`truncated` TINYINT(1) NOT NULL,
`userId` INT UNSIGNED NOT NULL,
`username` CHAR(15) NOT NULL,
`userFullname` CHAR(20) NOT NULL,
`media` VARCHAR(255) NULL COMMENT 'First encountered media of COALESCE',
`mediaType1` CHAR(5) NULL,
`media1` VARCHAR(255) NULL,
`mediaType2` CHAR(5) NULL,
`media2` VARCHAR(255) NULL,
`mediaType3` CHAR(5) NULL,
`media3` VARCHAR(255) NULL,
`mediaType4` CHAR(5) NULL,
`media4` VARCHAR(255) NULL,
PRIMARY KEY (`hashtagId`, `postId`),
INDEX `posts_coordinates` (`coordinates` ASC) COMMENT 'should be SPATIAL INDEX',
FULLTEXT INDEX `posts_twitter_search` (`text` ASC, `username` ASC, `userFullname` ASC),
CONSTRAINT `posts_twitter_posts`
FOREIGN KEY (`hashtagId` , `postId`)
REFERENCES `local_sysDB`.`posts` (`hashtagId` , `postId`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
This query results in:
Name Type Collation Attributes Null Default
hashtagId int(11) No None
postId char(20) utf8mb4_general_ci No None
coordinates point Yes NULL
created int(20) UNSIGNED No None
filterlevel varchar(45) utf8mb4_general_ci No None
language char(11) utf8mb4_general_ci No None
profanity tinyint(1) No None
retweeted tinyint(1) No None
text char(240) utf8mb4_general_ci No None
truncated tinyint(1) No None
userId int(10) UNSIGNED No None
username char(15) utf8mb4_general_ci No None
userFullname char(20) utf8mb4_general_ci No None
media varchar(255) utf8mb4_general_ci Yes NULL
mediaType1 char(5) utf8mb4_general_ci Yes NULL
media1 varchar(255) utf8mb4_general_ci Yes NULL
mediaType2 char(5) utf8mb4_general_ci Yes NULL
media2 varchar(255) utf8mb4_general_ci Yes NULL
mediaType3 char(5) utf8mb4_general_ci Yes NULL
media3 varchar(255) utf8mb4_general_ci Yes NULL
mediaType4 char(5) utf8mb4_general_ci Yes NULL
media4 varchar(255) utf8mb4_general_ci Yes NULL
When I run this query: SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
It gives me this result in phpMyAdmin:
Variable_name Value
character_set_client utf8
character_set_connection utf8
character_set_database utf8mb4
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
collation_connection utf8_general_ci
collation_database utf8mb4_general_ci
collation_server latin1_swedish_ci
I use the following MySQL version:
Server: Localhost via UNIX socket
Server type: MySQL Server version: 5.6.24-log - MySQL Community Server (GPL)
Protocol version: 10
Server charset: UTF-8 Unicode (utf8)
I've already found out that using a BLOB
field could help, but then I can't use a FULLTEXT
index on the field text
.
Is there a way to save emoji's to my database without transforming them into question marks?
This is not a duplicate, because my characters are not urning blank, but in question marks, also, solutions in the provided topic are not working.